package jsyf.oa.dao.report.newAppReport;

import com.alibaba.fastjson.JSONObject;
import jsyf.oa.idao.BaseDao;
import jsyf.oa.ientity.IBaseEntity;
import org.springframework.stereotype.Repository;

import java.util.List;

@SuppressWarnings("all")
@Repository
public class NewAppReportForZSQDao extends BaseDao {
    @Override
    public IBaseEntity convertJson2Entity(JSONObject json) {
        return null;
    }

    public List<JSONObject> getRegionManagerByUserId(String userId, String dateTime, StringBuffer condition) {
        StringBuffer sql = new StringBuffer();
        if (null == condition) {
            condition = new StringBuffer();
            condition.append(" AND roleName in ('区域经理','区域组长','大区经理')\n");
        }
        sql.append("SELECT v_sys_user.id as userId\n" +
                "FROM v_sys_user\n" +
                "LEFT JOIN t_sys_user_leave ON t_sys_user_leave.USER_ID = v_sys_user.id\n" +
                "WHERE 1=1\n");
        sql.append(condition);
        sql.append("\tAND v_sys_user.id in (" + userId + ")\n" +
                "AND (v_sys_user.jobState!=1 OR t_sys_user_leave.LEAVE_DATE='" + dateTime + "')\n\n" +
                "GROUP BY v_sys_user.id\n");
        return super.queryForJsonList(sql.toString());
    }

    public List<JSONObject> getTodayPlanByUserIdAndDateTimeForZSQ(String userIds, String dateTime) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\tsysUser.userName,\n" +
                "\tT_WRT_CUSTOMER_VIST_PLAN.CUSTOMER_ADDRESS AS customerAddress,\n" +
                "\tT_WRT_CUSTOMER_VIST_PLAN.ENTOURAGE AS entourage,\n" +
                "\tT_WRT_CUSTOMER_VIST_PLAN.VISIT_GOAL AS visitGoal,\n" +
                "\tT_WRT_CUSTOMER_VIST_PLAN.CUSTOMER_NAME AS customerName,\n" +
                "\tT_WRT_CUSTOMER_VIST_PLAN.COUNTY AS county,\n" +
                "\tCASE\n" +
                "WHEN t_sys_sign_in.ID IS NOT NULL THEN\n" +
                "\t1\n" +
                "WHEN t_sys_leave.ID IS NOT NULL THEN\n" +
                "\t0\n" +
                "WHEN t_sys_email.ID IS NOT NULL THEN\n" +
                "\t3\n" +
                "ELSE\n" +
                "\t2\n" +
                "END AS signState,\n" +
                "CASE WHEN t_sys_email.ID IS NOT NULL THEN\n" +
                "\t1\n" +
                "ELSE\n" +
                "0\n" +
                "END as reportState,\n" +
                " CASE\n" +
                "WHEN t_sys_sign_in.ID IS NOT NULL THEN\n" +
                "\t10\n" +
                "WHEN t_sys_leave.ID IS NOT NULL THEN\n" +
                "\t20\n" +
                "WHEN t_sys_email.ID IS NOT NULL THEN\n" +
                "\t30\n" +
                "ELSE\n" +
                "\t40\n" +
                "END signSort\n" +
                "FROM\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\tt_sys_user.ID AS userId,\n" +
                "\t\t\tt_sys_user.`NAME` AS userName,\n" +
                "\t\t\tt_sys_depart.`NAME` AS departName,\n" +
                "\t\t\tt_sys_company.`NAME` AS companyName,\n" +
                "\t\t\tt_sys_company.SORT_NO AS companySortNo,\n" +
                "\t\t\tt_sys_depart.SORT_NO AS departSortNo\n" +
                "\t\tFROM\n" +
                "\t\t\tt_sys_user\n" +
                "\t\tLEFT JOIN t_sys_role ON t_sys_user.ROLE_ID = t_sys_role.ID\n" +
                "\t\tLEFT JOIN t_sys_depart ON t_sys_role.DEPART_ID = t_sys_depart.ID\n" +
                "\t\tLEFT JOIN t_sys_company ON t_sys_depart.COMPANY_ID = t_sys_company.ID\n" +
                "\t\tWHERE\n" +
                "\t\t\tt_sys_user.ID IN (" + userIds + ")\n" +
                "\t\tAND t_sys_role.DEL_FLAG = 0\n" +
                "\t\tAND t_sys_depart.DEL_FLAG = 0\n" +
                "\t\tAND t_sys_company.DEL_FLAG = 0\n" +
                "\t) AS sysUser\n" +
                "LEFT JOIN T_WRT_CUSTOMER_VIST_PLAN ON sysUser.userId = T_WRT_CUSTOMER_VIST_PLAN.CREATE_USER_ID\n" +
                "AND T_WRT_CUSTOMER_VIST_PLAN.CREATE_USER_ID IN (" + userIds + ")\n" +
                "AND T_WRT_CUSTOMER_VIST_PLAN.REPORT_DATE = '" + dateTime + "'\n" +
                "LEFT JOIN t_sys_sign_in ON t_sys_sign_in.USER_ID = sysUser.userId\n" +
                "AND t_sys_sign_in.SIGN_DATE = '" + dateTime + "'\n" +
                "LEFT JOIN t_sys_leave ON t_sys_leave.USER_ID = sysUser.userId\n" +
                "AND t_sys_leave.LEAVE_DATE = '" + dateTime + "'\n" +
                "LEFT JOIN t_sys_email ON t_sys_email.CREATE_USER_ID = sysUser.userId\n" +
                "AND t_sys_email.REPORT_DATE = '" + dateTime + "'\n" +
                "AND t_sys_email.E_TYPE = 1\n" +
                "ORDER BY\n" +
                "\tsysUser.companySortNo,\n" +
                "\tsysUser.departSortNo,\n" +
                "\tsignSort,\n" +
                "sysUser.userId\n");
        return super.queryForJsonList(sql.toString());
    }

    public List<JSONObject> getSignAndLeaveForToday(String userId, String dateTime) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT\n" +
                "\tsysUser.*,\n" +
                "\tuserSign.signInAlwaysDistance,\n" +
                "\tuserSign.signOutAlwaysDistance,\n" +
                "\tuserSign.address,\n" +
                "\tuserSign.signOutTime,\n" +
                "\tuserSign.signOutAddress,\n" +
                "\tuserSign.latitude,\n" +
                "\tuserSign.longitude,\n" +
                "\tuserSign.signCreateTime,\n" +
                "\tuserSign.signOutUrl,\n" +
                "\tuserSign.signUrl,\n" +
                "\tuserLeave.beginTime,\n" +
                "\tuserLeave.endTime,\n" +
                "\tuserLeave.remark\n" +
                "FROM\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\tv_sys_user.ID AS userId,\n" +
                "\t\t\tv_sys_user.residence,\n" +
                "\t\t\tv_sys_user. NAME AS userName,\n" +
                "\t\tv_sys_user.departName ,\n" +
                "\t\t\tv_sys_user.roleName ,\n" +
                "\t\t\tv_sys_user.companyName ,\n" +
                "\tv_sys_user.companySortNo,\n" +
                "\tv_sys_user.departSortNo,\n" +
                "\tv_sys_user.roleNameLevel\n" +
                "\t\tFROM\n" +
                "\t\t\tv_sys_user\n" +
                "\t\tWHERE\n" +
                "\t\t\tv_sys_user.ID IN (" + userId + ")\n" +
                "\t) AS sysUser\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tt_sys_sign_in.USER_ID AS userId,\n" +
                "\t\tt_sys_sign_in.SIGN_URL AS signUrl,\n" +
                "\t\tt_sys_sign_in.SIGN_OUT_URL AS signOutUrl,\n" +
                "\t\tt_sys_sign_in.LONGITUDE AS longitude,\n" +
                "\tSUBSTRING(t_sys_sign_in.SIGN_OUT_TIME,12,5) AS signOutTime,\n" +
                "\t\tt_sys_sign_in.ADDRESS AS address,\n" +
                "\t\tt_sys_sign_in.SIGN_OUT_ADDRESS AS signOutAddress,\n" +
                "\t\tt_sys_sign_in.SIGN_OUT_ALWAYS_DISTANCE AS signOutAlwaysDistance,\n" +
                "\t\tt_sys_sign_in.SIGN_IN_ALWAYS_DISTANCE AS signInAlwaysDistance,\n" +
                "\t\tt_sys_sign_in.LATITUDE AS latitude,\n" +
                "\t\tSUBSTRING(\n" +
                "\t\t\tt_sys_sign_in.CREATE_TIME,\n" +
                "\t\t\t12,\n" +
                "\t\t\t5\n" +
                "\t\t) AS signCreateTime\n" +
                "\tFROM\n" +
                "\t\tt_sys_sign_in\n" +
                "\tWHERE\n" +
                "\t\t1 = 1\n" +
                "\tAND t_sys_sign_in.USER_ID IN (" + userId + ")\n" +
                "\tAND t_sys_sign_in.SIGN_DATE = '" + dateTime + "'\n" +
                ") AS userSign ON userSign.userId = sysUser.userId\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tLEFT (t_sys_leave.BEGIN_TIME, 10) AS beginTime,\n" +
                "\t\tLEFT (t_sys_leave.END_TIME, 10) AS endTime,\n" +
                "\t\tt_sys_leave.USER_ID AS userId,\n" +
                "\t\tt_sys_leave.REMARK AS remark\n" +
                "\tFROM\n" +
                "\t\tt_sys_leave\n" +
                "\tWHERE\n" +
                "\t\tt_sys_leave.USER_ID IN (" + userId + ")\n" +
                "\tAND LEFT (t_sys_leave.LEAVE_DATE, 10) = '" + dateTime + "'\n" +
                "GROUP BY t_sys_leave.USER_ID\n\n" +
                ") AS userLeave ON userLeave.userId = sysUser.userId\n" +
                "GROUP BY sysUser.userId,sysUser.roleName\n" +
                "ORDER BY\n " +
                "\tsysUser.companySortNo,\n" +
                "\tsysUser.departSortNo,\n" +
                "\tsysUser.roleNameLevel,\n" +
                "\tsysUser.userName\n");
        return super.queryForJsonList(sb.toString());
    }

    public List<JSONObject> getFirstVisitAndSignForToday(String userId, String dateTime) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\tsysUser.*,\n" +
                "\tt_sys_sign_in.LATITUDE AS signInLatitude,\n" +
                "\tt_sys_sign_in.LONGITUDE AS signInLongitude,\n" +
                "\tt_sys_sign_in.SIGN_IN_SPEED AS signInspeed,\n" +
                "\tt_sys_sign_in.CREATE_TIME as signTime,\n" +
                "\tt_sys_sign_in.SIGN_IN_TIME_DIFF AS signInDiff,\n" +
                "\tt_sys_sign_in.SIGN_IN_DISTANCE AS signInDistance,\n" +
                "\twrtArr.arrivalsTime,\n" +
                "\twrtArr.arrivalsLatitude,\n" +
                "\twrtArr.arrivalsLongitude\n" +
                "FROM\n" +
                "\t(SELECT\n" +
                "\t\t\tv_sys_user.companySortNo,\n" +
                "\t\t\tv_sys_user.departSortNo,\n" +
                "\t\t\tv_sys_user.roleNameLevel,\n" +
                "\t\t\tv_sys_user.`name` AS userName,\n" +
                "\t\t\tv_sys_user.`id` AS userId," +
                "\t\tv_sys_user.departName ,\n" +
                "\t\t\tv_sys_user.roleName ,\n" +
                "\t\t\tv_sys_user.companyName \n" +
                "\t\tFROM\n" +
                "\t\t\tv_sys_user\n" +
                "\t\tWHERE\n" +
                "\t\t\t1 = 1\n" +
                "\t\tAND v_sys_user.ID  IN (" + userId + ")\n" +
                "\t) AS sysUser\n" +
                "LEFT JOIN t_sys_sign_in ON t_sys_sign_in.CREATE_USER_ID = sysUser.userId\n" +
                "AND t_sys_sign_in.SIGN_DATE = '" + dateTime + "'\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tt_wrt_customer_visit.CREATE_USER_ID AS userId,\n" +
                "\t\tmin(\n" +
                "\t\t\tt_wrt_customer_visit.ARRIVALS_TIME\n" +
                "\t\t) AS arrivalsTime,\n" +
                "\t\tt_wrt_customer_visit.ARRIVALS_LATITUDE AS arrivalsLatitude,\n" +
                "\tt_wrt_customer_visit.ARRIVALS_LONGITUDE AS arrivalsLongitude\n" +
                "\tFROM\n" +
                "\t\tt_wrt_customer_visit\n" +
                "\tWHERE\n" +
                "\t\t1 = 1\n" +
                "\tAND t_wrt_customer_visit.VISIT_MODE = 1\n" +
                "\tAND t_wrt_customer_visit.REPORT_DATE = '" + dateTime + "'\n" +
                "\tGROUP BY\n" +
                "\t\tt_wrt_customer_visit.CREATE_USER_ID\n" +
                ") AS wrtArr ON wrtArr.userId = sysUser.userId\n" +
                "GROUP BY sysUser.userId,sysUser.roleName\n" +
                "ORDER BY \tsysUser.companySortNo,\n" +
                "\tsysUser.departSortNo,\n" +
                "\tsysUser.roleNameLevel,\n" +
                "\tsysUser.userName\n");
        return super.queryForJsonList(sql.toString());
    }

    public List<JSONObject> getForPersonalDayByUserIdAndDateTime(String userIds, String dateTime, Integer type, Integer status) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT\n" +
                "\tsysUser.*, \n" +
                "ifnull(faceCustomer.faceNum,0) as faceNum,\n" +
                "ifnull(telCustomer.telNum,0) as telNum\n" +
                "FROM\n" +
                "\t(\n" +
                "SELECT\n" +
                "\t\t\tv_sys_user.ID AS userId,\n" +
                "\t\t\tv_sys_user.residence,\n" +
                "\t\t\tv_sys_user. NAME AS userName,\n" +
                "\t\t\tv_sys_user.departName,\n" +
                "\t\t\tv_sys_user.roleName,\n" +
                "\t\t\tv_sys_user.companyName,\n" +
                "\t\t\tv_sys_user.companySortNo,\n" +
                "\t\t\tv_sys_user.departSortNo,\n" +
                "\t\t\tv_sys_user.roleNameLevel,\n" +
                "\t\t\tCASE\n" +
                "\t\tWHEN v_sys_sign_leave.signState IS NOT NULL THEN\n" +
                "\t\t\tv_sys_sign_leave.signState\n" +
                "\t\tELSE\n" +
                "\t\t\t2\n" +
                "\t\tEND AS signState\n" +
                "\t\tFROM\n" +
                "\t\t\tv_sys_user\n" +
                "\t\tLEFT JOIN v_sys_sign_leave ON v_sys_sign_leave.userId = v_sys_user.id\n" +
                "\t\tAND v_sys_sign_leave.signDate = '" + dateTime + "'\n" +
                "\t\tWHERE\n" +
                "\t\t\tv_sys_user.ID IN (" + userIds + ")\n" +
                "\t) AS sysUser\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tt_wrt_customer_visit.CREATE_USER_ID AS userId,\n" +
                "\t\tCOUNT(1) AS faceNum\n" +
                "\tFROM\n" +
                "\t\tt_wrt_customer_visit\n" +
                "\tLEFT JOIN t_wrt_customer ON t_wrt_customer_visit.CUSTOMER_ID = t_wrt_customer.ID\n" +
                "\tWHERE\n" +
                "\t\t1 = 1\n" +
                "\tAND t_wrt_customer_visit.VISIT_MODE = 1\n" +
                "AND t_wrt_customer_visit.CUSTOMER_ID is not null\n" +
                "\tAND t_wrt_customer_visit.REPORT_DATE = '" + dateTime + "'\n" +
                "\tAND t_wrt_customer_visit.CREATE_USER_ID IN (" + userIds + ")\n" +
                "\tAND (\n" +
                "\t\tsubstring(\n" +
                "\t\t\tt_wrt_customer_visit.ARRIVALS_TIME,\n" +
                "\t\t\t11,\n" +
                "\t\t\t3\n" +
                "\t\t) >= 12\n" +
                "\t) = " + status + "\n" +
                "\tGROUP BY\n" +
                "\t\tt_wrt_customer_visit.CREATE_USER_ID\n" +
                ") AS faceCustomer ON sysUser.userId = faceCustomer.userId \n" +
                "\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tt_wrt_customer_visit.CREATE_USER_ID AS userId,\n" +
                "\t\tCOUNT(1) AS telNum\n" +
                "\tFROM\n" +
                "\t\tt_wrt_customer_visit\n" +
                "\tLEFT JOIN t_wrt_customer ON t_wrt_customer_visit.CUSTOMER_ID = t_wrt_customer.ID\n" +
                "\tWHERE\n" +
                "\t\t1 = 1\n" +
                "\tAND t_wrt_customer_visit.VISIT_MODE = 0\n" +
                "\tAND t_wrt_customer_visit.REPORT_DATE = '" + dateTime + "'\n" +
                "\tAND t_wrt_customer_visit.CREATE_USER_ID IN (" + userIds + ")\n" +
                "\tAND (\n" +
                "\t\tsubstring(\n" +
                "\t\t\tt_wrt_customer_visit.ARRIVALS_TIME,\n" +
                "\t\t\t11,\n" +
                "\t\t\t3\n" +
                "\t\t) >= 12\n" +
                "\t) = " + status + "\n" +
                "\tGROUP BY\n" +
                "\t\tt_wrt_customer_visit.CREATE_USER_ID\n" +
                ") AS telCustomer ON sysUser.userId = telCustomer.userId \n" +
                "GROUP BY sysUser.userId,sysUser.roleName\n" +
                "ORDER BY\n" +
                "\tsysUser.companySortNo,\n" +
                "\tsysUser.departSortNo,\n" +
                "\tsysUser.roleNameLevel,\n" +
                "\tsysUser.userName");
        List<JSONObject> jsonList = super.queryForJsonList(sb.toString());
        return jsonList;
    }

    public List<JSONObject> getForPersonalCostByUserIdAndDateTime(String userIds, String dateTime) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\tv_sys_user.ID AS userId,\n" +
                "\tv_sys_user.`NAME` AS userName,\n" +
                "\t\t\tv_sys_user.departName,\n" +
                "\t\t\tv_sys_user.roleName,\n" +
                "\t\t\tv_sys_user.companyName,\n" +
                "\tt_wrt_cost.CREATE_USER_ID AS createUserId,\n" +
                "\tt_wrt_cost.CREATE_TIME AS createTime,\n" +
                "\tt_wrt_cost.COST_SMALL_NAME AS costSmallName,\n" +
                "\tt_wrt_cost.INVOICE_FLAG AS invoiceFlag,\n" +
                "\tt_wrt_cost.`START` AS bxState,\n" +
                "\tt_wrt_cost.REPORT_DATE AS date,\n" +
                "\tIFNULL(`t_wrt_cost`.`MONEY`, 0) AS `money`,\n" +
                "\tt_wrt_cost_detail.MILEAGE AS mileage,\n" +
                "\tt_wrt_cost_detail.IMG AS img,\n" +
                "\tt_wrt_cost_detail.COHABITATION AS cohabitation,\n" +
                "\tt_wrt_cost_detail.LICENSE AS license,\n" +
                "\tt_wrt_cost_detail.HOTEL_NAME AS hotelName,\n" +
                "\tt_wrt_cost_detail.HOTEL_TEL AS hotelTel,\n" +
                "\tt_wrt_cost_detail.RESIDENCE AS residence,\n" +
                "\tt_wrt_cost_detail.`HOST` AS hostUser,\n" +
                "\tt_wrt_cost_detail.GUESTS AS guests,\n" +
                "\tt_wrt_cost_detail.GUEST_NATURE AS guestNature,\n" +
                "\tt_wrt_cost_detail.RESTAURANT_NAME AS restaurantName,\n" +
                "\tt_wrt_cost_detail.RESTAURANT_TEL AS restaurantTel,\n" +
                "\tt_wrt_cost_detail.ENTERTAINMENT_VENUE AS entertainmentVenue,\n" +
                "\tt_wrt_cost_detail.PHONE AS phone,\n" +
                "\tt_wrt_cost_detail.BEFORE_OFFICE AS beforeOffice,\n" +
                "\tt_wrt_cost_detail.AFTER_OFFICE AS afterOffice,\n" +
                "\tt_wrt_cost_detail.OFFICE_MATTERS AS officemetters,\n" +
                "\tt_wrt_cost_detail.PREDICT_MONEY AS predictMoney,\n" +
                "\tt_wrt_cost.REMARK AS remark\n" +
                "FROM\n" +
                "\tt_wrt_cost\n" +
                "LEFT JOIN t_wrt_cost_detail ON t_wrt_cost.ID = t_wrt_cost_detail.FK_COST_ID\n" +
                "LEFT JOIN v_sys_user ON v_sys_user.ID = t_wrt_cost.CREATE_USER_ID\n" +
                "WHERE\n" +
                "\tt_wrt_cost.CREATE_USER_ID IN (" + userIds + ")\n" +
                "AND v_sys_user.jobState != 1\n" +
                "AND v_sys_user.main = 1\n" +
                "AND t_wrt_cost.REPORT_DATE = '" + dateTime + "'\n" +
                "ORDER BY\n" +
                "\tt_wrt_cost.CREATE_TIME\n");
        return queryForJsonList(sql.toString());
    }

    public List<JSONObject> getEmailByUserIdForZSQ(String userIds, String dateTime, String userId, StringBuffer condition) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "sysRegion.regionName,\n" +
                "sysRegion.departName,\n" +
                "CASE\n" +
                "WHEN locate(\n" +
                "\t'大区',\n" +
                "\tsysRegion.regionName\n" +
                ")  = 0 THEN\n" +
                "\t'区域经理'\n" +
                "WHEN locate(\n" +
                "\t'大区',\n" +
                "\tsysRegion.regionName\n" +
                ")  > 0 AND sysUser.roleName1 is NULL\n" +
                "THEN '大区经理'\n" +
                "ELSE\n" +
                "\tsysUser.roleName1\n" +
                "END AS roleName,\n" +
                "sysUser.*,\n" +
                "t_sys_user.`NAME` as readUserName,\n" +
                "\temail.WId,\n" +
                "\temail.emailId,\n" +
                "\tCASE\n" +
                "WHEN email.READER = '1' THEN\n" +
                "\t'已读'\n" +
                "ELSE\n" +
                "\t'未读'\n" +
                "END AS reader,\n" +
                " CASE\n" +
                "WHEN (\n" +
                "\temail.TITLE IS NOT NULL || email.TITLE != ''\n" +
                ") THEN\n" +
                "\t'已发送'\n" +
                "ELSE\n" +
                "\t'未发送'\n" +
                "END AS titleSend,\n" +
                " CASE\n" +
                "WHEN (\n" +
                "\tt_sys_leave.LEAVE_DATE IS NOT NULL || t_sys_leave.LEAVE_DATE != ''\n" +
                ") THEN\n" +
                "\t'请假'\n" +
                "ELSE\n" +
                "\t'未请假'\n" +
                "END AS leaveDate,\n" +
                " IFNULL(emailNum.replyNum, '0') AS replyNum\n" +
                "\n" +
                "FROM\n" +
                "(SELECT\n" +
                "\t\t\tt_sys_company.SORT_NO AS companySortNo,\n" +
                "\tt_sys_region.ID AS regionId,\n" +
                "\tt_sys_region.PARENT_ID,\n" +
                "\tt_sys_region.`NAME` AS regionName,\n" +
                "\tCASE\n" +
                "WHEN t_sys_depart.`NAME` IS NULL THEN\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\ta.`NAME`\n" +
                "\t\tFROM\n" +
                "\t\t\tt_sys_region AS a\n" +
                "\t\tWHERE\n" +
                "\t\t\ta.DEL_FLAG = 0\n" +
                "\t\tAND a.ID = t_sys_region.PARENT_ID\n" +
                "\t)\n" +
                "ELSE\n" +
                "\tt_sys_depart.`NAME`\n" +
                "END AS departName,\n" +
                "CASE\n" +
                "WHEN t_sys_region.PARENT_ID = 0 THEN\n" +
                "\t(t_sys_region.SORT_NO + 1) * 100\n" +
                "ELSE\n" +
                "\t(\n" +
                "\t\t(\n" +
                "\t\t\tSELECT\n" +
                "\t\t\t\ta.SORT_NO\n" +
                "\t\t\tFROM\n" +
                "\t\t\t\tt_sys_region AS a\n" +
                "\t\t\tWHERE\n" +
                "\t\t\t\ta.DEL_FLAG = 0\n" +
                "\t\t\tAND a.ID = t_sys_region.PARENT_ID\n" +
                "\t\t) + 1\n" +
                "\t) * 100 + t_sys_region.SORT_NO \n" +
                "END AS regionSortNo\n" +
                "FROM\n" +
                "\tt_sys_user_2_region\n" +
                "LEFT JOIN t_sys_region ON t_sys_region.ID = t_sys_user_2_region.REGION_ID\n" +
                "LEFT JOIN t_sys_depart ON t_sys_depart.`NAME` = t_sys_region.`NAME`\n" +
                "AND t_sys_region.COMPANY_ID = t_sys_depart.COMPANY_ID\n" +
                "AND t_sys_depart.DEL_FLAG = 0\n" +
                "LEFT JOIN t_sys_company ON t_sys_company.ID=t_sys_region.COMPANY_ID AND t_sys_company.DEL_FLAG=0\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "AND t_sys_user_2_region.USER_ID = '" + userId + "'\n" +
                "AND t_sys_region.DEL_FLAG = 0\n" +
                "AND t_sys_company.DEL_FLAG = 0\n" +
                "\t) AS sysRegion\n" +
                "\tLEFT JOIN (\n" +
                "\t\tSELECT\n" +
                "\t\t\tt_sys_user.ID AS userId,\n" +
                "\t\t\tt_sys_user.`NAME` AS userName,\n" +
                "\t\t\tt_sys_company.`NAME` AS companyName,\n" +
                "\t\t\tt_sys_role_name.`LEVEL` AS roleNameSortNo,\n" +
                "\t\t\tt_sys_role_name.`NAME` AS roleName1,\n" +
                "\t\t\tt_sys_depart.SORT_NO AS departSortNo,\n" +
                "\t\t\tt_sys_user.PARENT_ID AS userParentId," +
                "CASE\n" +
                "\tWHEN t_sys_role_name.`NAME` = '区域经理' THEN\n" +
                "\t\t(\n" +
                "\t\t\tSELECT\n" +
                "\t\t\t\tt_sys_region.`ID`\n" +
                "\t\t\tFROM\n" +
                "\t\t\t\tt_sys_user_2_region\n" +
                "\t\t\tLEFT JOIN t_sys_region ON t_sys_region.ID = t_sys_user_2_region.REGION_ID\n" +
                "\t\t\tWHERE\n" +
                "\t\t\t\t1 = 1\n" +
                "\t\t\tAND t_sys_region.DEL_FLAG = 0\n" +
                "\t\t\tAND t_sys_region.PARENT_ID != 0\n" +
                "\t\t\tAND t_sys_user_2_region.USER_ID = t_sys_user.ID\n" +
                "\t\t\tLIMIT 0,\n" +
                "\t\t\t1\n" +
                "\t\t)\n" +
                "\tELSE\n" +
                "\t\t(\n" +
                "\t\t\tSELECT\n" +
                "\t\t\t\tt_sys_region.`id`\n" +
                "\t\t\tFROM\n" +
                "\t\t\t\tt_sys_user_2_region\n" +
                "\t\t\tLEFT JOIN t_sys_region ON t_sys_region.ID = t_sys_user_2_region.REGION_ID\n" +
                "\t\t\tWHERE\n" +
                "\t\t\t\t1 = 1\n" +
                "\t\t\tAND t_sys_region.DEL_FLAG = 0\n" +
                "\t\t\tAND t_sys_region.PARENT_ID = 0\n" +
                "\t\t\tAND t_sys_user_2_region.USER_ID = t_sys_user.ID\n" +
                "\t\t\tLIMIT 0,\n" +
                "\t\t\t1\n" +
                "\t\t)\n" +
                "\tEND AS regionId\n" +
                "\t\tFROM\n" +
                "\t\t\tt_sys_user\n" +
                "\t\tLEFT JOIN t_sys_role ON t_sys_role.ID = t_sys_user.ROLE_ID\n" +
                "\t\tLEFT JOIN t_sys_role_name ON t_sys_role_name.ID = t_sys_role.ROLE_NAME_ID\n" +
                "\t\tLEFT JOIN t_sys_depart ON t_sys_depart.ID = t_sys_role.DEPART_ID\n" +
                "\t\tLEFT JOIN t_sys_company ON t_sys_company.ID = t_sys_depart.COMPANY_ID\n" +
                "\t\tWHERE\n" +
                "\t\t\t1 = 1\n" +
                "\t\tAND t_sys_role.DEL_FLAG = 0\n" +
                "\t\tAND t_sys_company.DEL_FLAG = 0\n" +
                "\t\tAND t_sys_role_name.DEL_FLAG = 0\n" +
                "\t\tAND t_sys_depart.DEL_FLAG = 0\n" +
                "\t\tAND t_sys_user.ID IN (" + userIds + ")\n" +
                "\t) AS sysUser  ON sysRegion.regionId = sysUser.regionId\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tt_wrt_main.ID AS id,\n" +
                "\t\tt_wrt_main.USER_ID AS userId,\n" +
                "\t\tt_sys_email_receiver.READER,\n" +
                "\t\tt_wrt_main.TITLE,\n" +
                "\t\tt_sys_email.ID AS emailId,\n" +
                "\t\tt_sys_email.DATA_ID AS WId,\n" +
                "\t\tt_sys_email_receiver.USER_ID AS receiverUserId\n" +
                "\tFROM\n" +
                "\t\tt_wrt_main\n" +
                "\tLEFT JOIN t_sys_email ON t_wrt_main.ID = t_sys_email.DATA_ID\n" +
                "\tLEFT JOIN t_sys_email_receiver ON t_sys_email.DATA_ID = t_sys_email_receiver.DATA_ID\n" +
                "\tWHERE\n" +
                "\t\tt_wrt_main.USER_ID IN (" + userIds + ")\n" +
                "\tAND t_wrt_main.flage !=- 1\n" +
                "\tAND t_sys_email.E_TYPE = 1\n" +
                "\tAND t_sys_email_receiver.USER_TYPE = 0\n" +
                "\tAND t_wrt_main.REPORT_DATE = '" + dateTime + "'\n" +
                ") AS email ON email.userId = sysUser.userId\n" +
                "AND sysUser.userParentId = email.receiverUserId\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tCOUNT(1) AS replyNum,\n" +
                "\t\tDATA_ID\n" +
                "\tFROM\n" +
                "\t\tt_sys_email_receiver\n" +
                "\tWHERE\n" +
                "\t\t1 = 1\n" +
                "\tAND REPLY_TIME IS NOT NULL\n" +
                "\tAND DATA_ID IN (\n" +
                "\t\tSELECT\n" +
                "\t\t\tt_wrt_main.ID\n" +
                "\t\tFROM\n" +
                "\t\t\tt_wrt_main\n" +
                "\t\tWHERE\n" +
                "\t\t\tt_wrt_main.USER_ID IN (" + userIds + ")\n" +
                "\t\tAND t_wrt_main.REPORT_DATE = '" + dateTime + "'\n" +
                "\t)\n" +
                "\tGROUP BY\n" +
                "\t\tDATA_ID\n" +
                ") AS emailNum ON email.WId = emailNum.DATA_ID\n" +
                "LEFT JOIN t_sys_leave ON sysUser.userId = t_sys_leave.USER_ID\n" +
                "AND LEAVE_DATE = '" + dateTime + "'\n" +
                "LEFT JOIN t_sys_user ON t_sys_user.id=sysUser.userParentId\n" +
                "ORDER BY\n" +
                "\tsysRegion.companySortNo,\n" +
                "\tsysRegion.regionSortNo,\n" +
                "\tsysUser.departSortNo,\n" +
                "\tsysUser.roleNameSortNo,\n" +
                "\tsysUser.userName\n\n");
        return super.queryForJsonList(sql.toString());
    }

    public List<JSONObject> getLeaveReasonByUserId(String userId, String dateTime) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\tLEFT (t_sys_leave.BEGIN_TIME, 10) AS beginTime,\n" +
                "\tLEFT (t_sys_leave.END_TIME, 10) AS endTime,\n" +
                "\tt_sys_leave.USER_ID AS userId,\n" +
                "\tt_sys_leave.REMARK AS remark\n" +
                "FROM\n" +
                "\tt_sys_leave\n" +
                "WHERE\n" +
                "\tt_sys_leave.USER_ID ='" + userId + "'\n" +
                "AND LEFT (t_sys_leave.LEAVE_DATE, 10) = '" + dateTime + "'\n" +
                "GROUP BY\n" +
                "\tt_sys_leave.USER_ID\n");
        return super.queryForJsonList(sql.toString());
    }

    public List<JSONObject> getForPersonalDayByUserIdAndDateTime(String userId, String dateTime, Integer type, Integer status, StringBuffer condition, Integer requestPage, Integer pageSize) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT customer.* FROM(\n" +
                "SELECT t_wrt_customer.`NAME` as `customerName`\n" +
                ",t_wrt_customer.ID as customerId\n" +
                ",t_wrt_customer.TEL as customerTel\n" +
                ",t_wrt_customer.WECHAT as customerWechat\n" +
                ",t_wrt_customer_visit.ENTOURAGE as entourageName\n" +
                ",t_wrt_customer_visit.ENTOURAGE_ID as entourageId\n" +
                ",t_wrt_customer_visit.CUSTOMER_TYPE as customerType\n" +
                ",t_wrt_customer_visit.CUSTOMER_CREDIT AS customerCredit\n" +
                ",t_wrt_customer_visit.ARRIVALS_TIME AS arrivalsTime\n" +
                ",t_wrt_customer_visit.LEAVE_TIME AS leaveTime\n" +
                ",t_wrt_customer_visit.LEAVE_IMG as leaveImg\n" +
                ",t_wrt_customer_visit.ARRIVALS_IMG AS arrivalsImg\n" +
                ",t_wrt_customer_visit.OTHER_CONDITION as otherCondition\n" +
                ",substring(t_wrt_customer_visit.ARRIVALS_TIME,11,3)>=12 as status\n" +
                ",IFNULL(CEIL(CEILING(UNIX_TIMESTAMP(t_wrt_customer_visit.LEAVE_TIME)-UNIX_TIMESTAMP(t_wrt_customer_visit.ARRIVALS_TIME))/60),0) as time \n" +
                "FROM t_wrt_customer_visit\n" +
                "LEFT JOIN t_wrt_customer ON t_wrt_customer_visit.CUSTOMER_ID=t_wrt_customer.ID\n" +
                "WHERE 1=1\n" +
                "AND t_wrt_customer_visit.VISIT_MODE=" + type + "\n" +
                "AND t_wrt_customer_visit.CUSTOMER_ID is not null\n" +
                "AND t_wrt_customer_visit.REPORT_DATE='" + dateTime + "'\n");
        if (null != condition) {
            sql.append(condition);
        }
        sql.append("AND t_wrt_customer_visit.CREATE_USER_ID='" + userId + "') AS customer\n");
        sql.append("WHERE customer.status=" + status + "\n");
        sql.append("ORDER BY customer.arrivalsTime\n");
        List<JSONObject> jsonList = super.queryForJsonList(sql.toString());
        return jsonList;
    }

    public Integer getForPersonalDayByUserIdAndDateTimePageCount(String userId, String dateTime, Integer type, Integer status, StringBuffer condition) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT count(1) as count from(  SELECT customer.* FROM(\n" +
                "SELECT t_wrt_customer.`NAME` as `customerName`\n" +
                ",t_wrt_customer.ID as customerId\n" +
                ",t_wrt_customer.TEL as customerTel\n" +
                ",t_wrt_customer.WECHAT as customerWechat\n" +
                ",t_wrt_customer_visit.ENTOURAGE as entourageName\n" +
                ",t_wrt_customer_visit.ENTOURAGE_ID as entourageId\n" +
                ",t_wrt_customer_visit.CUSTOMER_TYPE as customerType\n" +
                ",t_wrt_customer_visit.CUSTOMER_CREDIT AS customerCredit\n" +
                ",t_wrt_customer_visit.ARRIVALS_TIME AS arrivalsTime\n" +
                ",t_wrt_customer_visit.LEAVE_TIME AS leaveTime\n" +
                ",t_wrt_customer_visit.LEAVE_IMG as leaveImg\n" +
                ",t_wrt_customer_visit.ARRIVALS_IMG AS arrivalsImg\n" +
                ",t_wrt_customer_visit.OTHER_CONDITION as otherCondition\n" +
                ",substring(t_wrt_customer_visit.ARRIVALS_TIME,11,3)>=12 as status\n" +
                ",IFNULL(CEIL(CEILING(UNIX_TIMESTAMP(t_wrt_customer_visit.LEAVE_TIME)-UNIX_TIMESTAMP(t_wrt_customer_visit.ARRIVALS_TIME))/60),0) as time \n" +
                "FROM t_wrt_customer_visit\n" +
                "LEFT JOIN t_wrt_customer ON t_wrt_customer_visit.CUSTOMER_ID=t_wrt_customer.ID\n" +
                "WHERE 1=1\n" +
                "AND t_wrt_customer_visit.VISIT_MODE=" + type + "\n" +
                "AND t_wrt_customer_visit.CUSTOMER_ID is not null\n" +
                "AND t_wrt_customer_visit.REPORT_DATE='" + dateTime + "'\n");
        if (null != condition) {
            sql.append(condition);
        }
        sql.append("AND t_wrt_customer_visit.CREATE_USER_ID='" + userId + "') AS customer\n");
        sql.append("WHERE customer.status=" + status + "\n");
        sql.append(") as num\n");
        String count = super.queryForString(sql.toString(), new Object[]{});
        return Integer.parseInt(count);
    }

    //人做主表
    public List<JSONObject> getUserRegionEmailForZSQ(String userId, String senderIds, String receiverIds, String dateTime) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT * FROM\n" +
                "(\n" +
                "SELECT\n" +
                "\tsysRegion.regionName,\n" +
                "\tsysRegion.companySortNo,\n" +
                "\tsysRegion.regionSortNo,\n" +
                "\tsysRegion.regionId,\n" +
                "\tsysRegion.companyId,\n" +
                "\tsysUser.userId,\n" +
                "\ta.`NAME` AS readerName,\n" +
                "\ta.`ID` AS readerId,\n" +
                "\tb.PARENT_ID AS senderParentId,\n" +
                "\tsysUser.userName,\n" +
                "\temail.WId,\n" +
                "\temail.emailId,\n" +
                "\tCASE\n" +
                "WHEN email.READER = '1' THEN\n" +
                "\t'已读'\n" +
                "ELSE\n" +
                "\t'未读'\n" +
                "END AS reader,\n" +
                " CASE\n" +
                "WHEN (\n" +
                "\temail.TITLE IS NOT NULL || email.TITLE != ''\n" +
                ") THEN\n" +
                "\t'已发送'\n" +
                "ELSE\n" +
                "\t'未发送'\n" +
                "END AS titleSend,\n" +
                " CASE\n" +
                "WHEN (\n" +
                "\tt_sys_leave.id IS NOT NULL \n" +
                ") THEN\n" +
                "\t'请假'\n" +
                "WHEN (\n" +
                "\tt_sys_sign_in.id IS  NULL \n" +
                ") THEN\n" +
                "\t'缺勤'\n" +
                "ELSE\n" +
                "\t'出勤'\n" +
                "END AS leaveDate,\n" +
                " IFNULL(emailNum.replyNum, '0') AS replyNum\n" +
                "FROM\n" +
                "(\n" +
                "\tSELECT\n" +
                "\t\tt_sys_user.ID AS userId,\n" +
                "\t\tt_sys_depart.`NAME` AS departName,\n" +
                "\t\tt_sys_user.`NAME` AS userName,\n" +
                "\t\tt_sys_company.id AS companyId\n" +
                "\tFROM\n" +
                "\t\tt_sys_user\n" +
                "\tLEFT JOIN t_sys_role ON t_sys_role.ID=t_sys_user.ROLE_ID\n" +
                "\tLEFT JOIN t_sys_depart ON t_sys_depart.ID = t_sys_role.DEPART_ID\n" +
                "\tLEFT JOIN t_sys_company ON t_sys_company.ID = t_sys_depart.COMPANY_ID\n" +
                "\tWHERE\n" +
                "\t\t1 = 1\n" +
                "\tAND t_sys_company.DEL_FLAG = 0\n" +
                "\tAND t_sys_depart.DEL_FLAG = 0\n" +
                "\tAND t_sys_role.DEL_FLAG = 0\n" +
                "\tAND t_sys_user.ID IN (" + senderIds + ")\n" +
                ") AS sysUser \n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tt_sys_email.CREATE_USER_ID AS senderId,\n" +
                "\t\tt_sys_email_receiver.USER_ID AS receiverId,\n" +
                "\t\tt_wrt_main.ID AS id,\n" +
                "\t\tt_sys_email_receiver.READER,\n" +
                "\t\tt_wrt_main.TITLE,\n" +
                "\t\tt_sys_email.ID AS emailId,\n" +
                "\t\tt_sys_email.DATA_ID AS WId\n" +
                "\tFROM\n" +
                "\t\tt_sys_email_receiver\n" +
                "\tLEFT JOIN t_sys_email ON t_sys_email_receiver.EMAIL_ID = t_sys_email.ID\n" +
                "\tLEFT JOIN t_wrt_main ON t_wrt_main.ID = t_sys_email.DATA_ID\n" +
                "\tWHERE\n" +
                "\t\t1 = 1\n" +
                "\tAND t_wrt_main.flage !=- 1\n" +
                "\tAND t_sys_email.E_TYPE = 1\n" +
                "\tAND t_sys_email_receiver.USER_ID IN (" + receiverIds + ")\n" +
                "\tAND t_sys_email.CREATE_USER_ID IN (" + senderIds + ")\n" +
                "\tAND t_sys_email.REPORT_DATE = '" + dateTime + "'\n" +
                ") AS email ON email.senderId = sysUser.userId\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tCOUNT(1) AS replyNum,\n" +
                "\t\tDATA_ID\n" +
                "\tFROM\n" +
                "\t\tt_sys_email_receiver\n" +
                "\tWHERE\n" +
                "\t\t1 = 1\n" +
                "\tAND REPLY_TIME IS NOT NULL\n" +
                "\tAND DATA_ID IN (\n" +
                "\t\tSELECT\n" +
                "\t\t\tt_wrt_main.ID\n" +
                "\t\tFROM\n" +
                "\t\t\tt_wrt_main\n" +
                "\t\tWHERE\n" +
                "\t\t\tt_wrt_main.USER_ID IN (" + senderIds + ")\n" +
                "\t\tAND t_wrt_main.REPORT_DATE = '" + dateTime + "'\n" +
                "\t\t AND t_sys_email_receiver.USER_ID IN (" + receiverIds + ") \n" +
                "\t)\n" +
                "\tGROUP BY\n" +
                "\t\tDATA_ID\n" +
                ") AS emailNum ON email.WId = emailNum.DATA_ID\n" +
                "\tLEFT JOIN (\n" +
                "\t\tSELECT\n" +
                "\t\t\tt_sys_region.`NAME` AS regionName,\n" +
                "\t\t\tt_sys_region.`ID` AS regionId,\n" +
                "\t\t\tt_sys_user_2_region.user_id AS userId,\n" +
                "\t\t\tt_sys_region.SORT_NO AS regionSortNo,\n" +
                "\t\t\tt_sys_company.SORT_NO AS companySortNo,\n" +
                "\t\t\tt_sys_region.COMPANY_ID AS companyId\n" +
                "\t\tFROM\n" +
                "\t\t\tt_sys_user_2_region\n" +
                "\t\tLEFT JOIN t_sys_region ON t_sys_region.id = t_sys_user_2_region.REGION_ID\n" +
                "\t\tLEFT JOIN t_sys_company ON t_sys_company.ID = t_sys_region.COMPANY_ID\n" +
                "\t\tAND t_sys_company.DEL_FLAG = 0\n" +
                "\t\tWHERE 1=1 \n" +
                "\t\tAND t_sys_user_2_region.user_id in (" + senderIds + ")\n" +
                "\t\tAND t_sys_region.DEL_FLAG = 0\n" +
                "\t\tAND t_sys_region.PARENT_ID = 0\n" +
                "\t) AS sysRegion on sysUser.userId=sysRegion.userId\n" +
                "LEFT JOIN t_sys_leave ON sysUser.userId = t_sys_leave.USER_ID\n" +
                "AND LEAVE_DATE = '" + dateTime + "'\n" +
                "LEFT JOIN t_sys_sign_in ON sysUser.userId = t_sys_sign_in.USER_ID\n" +
                "AND SIGN_DATE = '" + dateTime + "'\n" +
                "LEFT JOIN t_sys_user AS a ON a.ID = email.receiverId\n" +
                "LEFT JOIN t_sys_user AS b ON b.ID = sysUser.userId\n" +
                "ORDER BY\n" +
                "\tsysRegion.companySortNo,\n" +
                "\tsysRegion.regionSortNo,\n" +
                "\temail.receiverId) as obj\n" +
                "WHERE 1=1\n" +
                "AND (obj.senderParentId=obj.readerId OR obj.readerId IS null)\n" +
                "ORDER BY obj.companySortNo,\n" +
                "\tobj.regionSortNo,\n" +
                "\tobj.readerId,\n" +
                "\tobj.userId\n");
        return super.queryForJsonList(sql.toString());
    }

    public List<JSONObject> getRegionIdByUserIdForZSQ(String userId) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\tt_sys_company.SORT_NO AS companySortNo,\n" +
                "\tt_sys_region.SORT_NO AS regionSortNo,\n" +
                "\tt_sys_region.ID AS regionId,\n" +
                "\tt_sys_region.`NAME` AS regionName\n" +
                "FROM\n" +
                "\tt_sys_user_2_region\n" +
                "LEFT JOIN t_sys_region ON t_sys_region.ID = t_sys_user_2_region.REGION_ID\n" +
                "LEFT JOIN t_sys_company ON t_sys_company.ID = t_sys_region.COMPANY_ID\n" +
                "LEFT JOIN t_sys_depart ON t_sys_depart.`NAME` = t_sys_region.`NAME`\n " +
                "AND t_sys_depart.COMPANY_ID=t_sys_region.COMPANY_ID\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "AND t_sys_depart.DEL_FLAG = 0\n" +
                "AND t_sys_region.DEL_FLAG = 0\n" +
                "AND t_sys_company.DEL_FLAG = 0\n" +
                "AND t_sys_region.PARENT_ID = 0\n" +
                "AND t_sys_user_2_region.USER_ID = '" + userId + "'\n" +
                "ORDER BY\n" +
                "\tcompanySortNo,\n" +
                "\tregionSortNo\n");
        return super.queryForJsonList(sql.toString());
    }

    public List<JSONObject> getRmIdByRegionId(String regionId, String roleName, String userIds) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\tt_sys_user.id AS userId\n" +
                "FROM\n" +
                "\tt_sys_user_2_region\n" +
                "LEFT JOIN t_sys_region ON t_sys_region.ID=t_sys_user_2_region.REGION_ID\n" +
                "LEFT JOIN t_sys_user ON t_sys_user.ID=t_sys_user_2_region.USER_ID\n" +
                "LEFT JOIN t_sys_role ON t_sys_role.ID=t_sys_user.ROLE_ID\n" +
                "LEFT JOIN t_sys_role_name ON t_sys_role_name.ID=t_sys_role.ROLE_NAME_ID\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "AND t_sys_role.DEL_FLAG = 0\n" +
                "AND t_sys_user.JOB_STATE!=1\n" +
                "AND t_sys_role_name.DEL_FLAG = 0\n" +
                "AND t_sys_region.DEL_FLAG = 0\n" +
                "AND t_sys_user_2_region.REGION_ID = '" + regionId + "'\n" +
                "AND t_sys_role_name.`NAME` in (" + roleName + ")" +
                "AND t_sys_user_2_region.USER_ID IN (" + userIds + ")\n" +
                "GROUP BY t_sys_user.id\n");
        return super.queryForJsonList(sql.toString());
    }

    public List<JSONObject> getNoUserRegionReader(String regionId, String roleNames, String companyId) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\tv_sys_user.id as userId,\n" +
                "\tv_sys_user.`name` as userName,\n" +
                "\tv_sys_user.roleName,\n" +
                "\tv_sys_user.companyId,\n" +
                "\tv_sys_user.roleNameLevel,\n" +
                "v_sys_user.main\n" +
                "FROM\n" +
                "\tv_sys_user\n" +
                "LEFT JOIN t_sys_user_2_region ON t_sys_user_2_region.USER_ID = v_sys_user.id\n" +
                "LEFT JOIN t_sys_region ON t_sys_user_2_region.REGION_ID = t_sys_region.id\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "AND t_sys_region.DEL_FLAG = 0\n" +
                "AND t_sys_region.PARENT_ID = 0\n" +
                "AND v_sys_user.jobState != 1\n" +
                "AND v_sys_user.companyId = '" + companyId + "'\n" +
                "AND t_sys_user_2_region.REGION_ID = '" + regionId + "'\n" +
                "AND v_sys_user.roleName IN (" + roleNames + ")\n" +
                "ORDER BY roleNameLevel desc,main desc\n");
        return super.queryForJsonList(sql.toString());
    }

    public List<JSONObject> getRDEmailForZSQ(String userId, String senderIds, String receiverIds, String dateTime) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT * FROM\n" +
                "(\n" +
                "SELECT\n" +
                "\tsysRegion.regionName,\n" +
                "\tsysRegion.companySortNo,\n" +
                "\tsysRegion.regionSortNo,\n" +
                "\tsysRegion.regionId,\n" +
                "\tsysRegion.companyId,\n" +
                "\tsysUser.userId,\n" +
                "\ta.`NAME` AS readerName,\n" +
                "\ta.`ID` AS readerId,\n" +
                "\tb.PARENT_ID AS senderParentId,\n" +
                "\tsysUser.userName,\n" +
                "\temail.WId,\n" +
                "\temail.emailId,\n" +
                "\tCASE\n" +
                "WHEN email.READER = '1' THEN\n" +
                "\t'已读'\n" +
                "ELSE\n" +
                "\t'未读'\n" +
                "END AS reader,\n" +
                " CASE\n" +
                "WHEN (\n" +
                "\temail.TITLE IS NOT NULL || email.TITLE != ''\n" +
                ") THEN\n" +
                "\t'已发送'\n" +
                "ELSE\n" +
                "\t'未发送'\n" +
                "END AS titleSend,\n" +
                " CASE\n" +
                "WHEN (\n" +
                "\tt_sys_leave.LEAVE_DATE IS NOT NULL || t_sys_leave.LEAVE_DATE != ''\n" +
                ") THEN\n" +
                "\t'请假'\n" +
                "ELSE\n" +
                "\t'未请假'\n" +
                "END AS leaveDate,\n" +
                " IFNULL(emailNum.replyNum, '0') AS replyNum\n" +
                "FROM\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\tt_sys_region.`NAME` AS regionName,\n" +
                "\t\t\tt_sys_region.`ID` AS regionId,\n" +
                "\t\t\tt_sys_region.SORT_NO AS regionSortNo,\n" +
                "\t\t\tt_sys_company.SORT_NO AS companySortNo,\n" +
                "\t\t\tt_sys_region.COMPANY_ID AS companyId\n" +
                "\t\tFROM\n" +
                "\t\t\tt_sys_user_2_region\n" +
                "\t\tLEFT JOIN t_sys_region ON t_sys_region.id = t_sys_user_2_region.REGION_ID\n" +
                "\t\tLEFT JOIN t_sys_company ON t_sys_company.ID = t_sys_region.COMPANY_ID\n" +
                "\t\tAND t_sys_company.DEL_FLAG = 0\n" +
                "\t\tWHERE\n" +
                "\t\t\tt_sys_user_2_region.USER_ID = '" + userId + "'\n" +
                "\t\tAND t_sys_region.DEL_FLAG = 0\n" +
                "\t\tAND t_sys_region.PARENT_ID = 0\n" +
                "\t) AS sysRegion\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tt_sys_user.ID AS userId,\n" +
                "\t\tt_sys_depart.`NAME` AS departName,\n" +
                "\t\tt_sys_user.`NAME` AS userName,\n" +
                "\t\tt_sys_company.id AS companyId\n" +
                "\tFROM\n" +
                "\t\tt_sys_user\n" +
                "\tLEFT JOIN t_sys_role ON t_sys_role.ID=t_sys_user.ROLE_ID\n" +
                "\tLEFT JOIN t_sys_depart ON t_sys_depart.ID = t_sys_role.DEPART_ID\n" +
                "\tLEFT JOIN t_sys_company ON t_sys_company.ID = t_sys_depart.COMPANY_ID\n" +
                "\tWHERE\n" +
                "\t\t1 = 1\n" +
                "\tAND t_sys_company.DEL_FLAG = 0\n" +
                "\tAND t_sys_depart.DEL_FLAG = 0\n" +
                "\tAND t_sys_role.DEL_FLAG = 0\n" +
                "\tAND t_sys_user.ID IN (" + senderIds + ")\n" +
                ") AS sysUser ON sysUser.departName = sysRegion.regionName\n" +
                "AND sysRegion.companyId = sysUser.companyId\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tt_sys_email.CREATE_USER_ID AS senderId,\n" +
                "\t\tt_sys_email_receiver.USER_ID AS receiverId,\n" +
                "\t\tt_wrt_main.ID AS id,\n" +
                "\t\tt_sys_email_receiver.READER,\n" +
                "\t\tt_wrt_main.TITLE,\n" +
                "\t\tt_sys_email.ID AS emailId,\n" +
                "\t\tt_sys_email.DATA_ID AS WId\n" +
                "\tFROM\n" +
                "\t\tt_sys_email_receiver\n" +
                "\tLEFT JOIN t_sys_email ON t_sys_email_receiver.EMAIL_ID = t_sys_email.ID\n" +
                "\tLEFT JOIN t_wrt_main ON t_wrt_main.ID = t_sys_email.DATA_ID\n" +
                "\tWHERE\n" +
                "\t\t1 = 1\n" +
                "\tAND t_wrt_main.flage !=- 1\n" +
                "\tAND t_sys_email.E_TYPE = 1\n" +
                "\tAND t_sys_email_receiver.USER_ID IN (" + receiverIds + ")\n" +
                "\tAND t_sys_email.CREATE_USER_ID IN (" + senderIds + ")\n" +
                "\tAND t_sys_email.REPORT_DATE = '" + dateTime + "'\n" +
                ") AS email ON email.senderId = sysUser.userId\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tCOUNT(1) AS replyNum,\n" +
                "\t\tDATA_ID\n" +
                "\tFROM\n" +
                "\t\tt_sys_email_receiver\n" +
                "\tWHERE\n" +
                "\t\t1 = 1\n" +
                "\tAND REPLY_TIME IS NOT NULL\n" +
                "\tAND DATA_ID IN (\n" +
                "\t\tSELECT\n" +
                "\t\t\tt_wrt_main.ID\n" +
                "\t\tFROM\n" +
                "\t\t\tt_wrt_main\n" +
                "\t\tWHERE\n" +
                "\t\t\tt_wrt_main.USER_ID IN (" + senderIds + ")\n" +
                "\t\tAND t_wrt_main.REPORT_DATE = '" + dateTime + "'\n" +
                "\t\t AND t_sys_email_receiver.USER_ID IN (" + receiverIds + ") \n" +
                "\t)\n" +
                "\tGROUP BY\n" +
                "\t\tDATA_ID\n" +
                ") AS emailNum ON email.WId = emailNum.DATA_ID\n" +
                "LEFT JOIN t_sys_leave ON sysUser.userId = t_sys_leave.USER_ID\n" +
                "AND LEAVE_DATE = '" + dateTime + "'\n" +
                "LEFT JOIN t_sys_user AS a ON a.ID = email.receiverId\n" +
                "LEFT JOIN t_sys_user AS b ON b.ID = email.senderId\n" +
                "ORDER BY\n" +
                "\tsysRegion.companySortNo,\n" +
                "\tsysRegion.regionSortNo,\n" +
                "\temail.receiverId) as obj\n" +
                "WHERE 1=1\n" +
                "AND (obj.senderParentId=obj.readerId OR obj.readerId IS null)\n" +
                "ORDER BY obj.companySortNo,\n" +
                "\tobj.regionSortNo,\n" +
                "\tobj.readerId\n");
        return super.queryForJsonList(sql.toString());
    }

    //人做主表
    public List<JSONObject> getEmailForSupport(String userId, String senderIds, String receiverIds, String dateTime) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "sysRegion.regionName,\n" +
                "sysRegion.regionId,\n" +
                "sysRegion.companyId,\n" +
                "\ta.`NAME` AS readerName,\n" +
                "\ta.`ID` AS readerId,\n" +
                "\tb.`NAME` AS userName,\n" +
                "\tb.`ID` AS userId,\n" +
                "\temail.WId,\n" +
                "\temail.emailId,\n" +
                "\tCASE\n" +
                "WHEN email.READER = '1' THEN\n" +
                "\t'已读'\n" +
                "ELSE\n" +
                "\t'未读'\n" +
                "END AS reader,\n" +
                " CASE\n" +
                "WHEN (\n" +
                "\temail.TITLE IS NOT NULL || email.TITLE != ''\n" +
                ") THEN\n" +
                "\t'已发送'\n" +
                "ELSE\n" +
                "\t'未发送'\n" +
                "END AS titleSend,\n" +
                " CASE\n" +
                "WHEN (\n" +
                "\tt_sys_leave.LEAVE_DATE IS NOT NULL || t_sys_leave.LEAVE_DATE != ''\n" +
                ") THEN\n" +
                "\t'请假'\n" +
                "ELSE\n" +
                "\t'未请假'\n" +
                "END AS leaveDate,\n" +
                " IFNULL(emailNum.replyNum, '0') AS replyNum\n" +
                "FROM\n" +
                " (\n" +
                "\tSELECT\n" +
                "\t\tt_sys_user.ID AS userId,\n" +
                "\t\tt_sys_user_2_region.REGION_ID AS regionId,\n" +
                "\t\tt_sys_user.`NAME` AS userName\n" +
                "\tFROM\n" +
                "\t\t\tt_sys_user_2_region\n" +
                "LEFT JOIN t_sys_user ON t_sys_user.id=t_sys_user_2_region.USER_ID\n" +
                "\t\tLEFT JOIN t_sys_region ON t_sys_region.ID = t_sys_user_2_region.REGION_ID\n" +
                "\tWHERE\n" +
                "\t\t1 = 1\n" +
                "\tAND t_sys_region.DEL_FLAG = 0\n" +
                "\tAND t_sys_region.PARENT_ID = 0\n" +
                "\tAND t_sys_user_2_region.USER_ID IN (" + senderIds + ")\n" +
                ") AS sysUser\n" +
                "LEFT JOIN\n(\n" +
                "\t\tSELECT\n" +
                "\t\t\tt_sys_email.CREATE_USER_ID AS senderId,\n" +
                "\t\t\tt_sys_email_receiver.USER_ID AS receiverId,\n" +
                "\t\t\tt_wrt_main.ID AS id,\n" +
                "\t\t\tt_sys_email_receiver.READER,\n" +
                "\t\t\tt_wrt_main.TITLE,\n" +
                "\t\t\tt_sys_email.ID AS emailId,\n" +
                "\t\t\tt_sys_email.DATA_ID AS WId\n" +
                "\t\tFROM\n" +
                "\t\t\tt_sys_email_receiver\n" +
                "\t\tLEFT JOIN t_sys_email ON t_sys_email_receiver.EMAIL_ID = t_sys_email.ID\n" +
                "\t\tLEFT JOIN t_wrt_main ON t_wrt_main.ID = t_sys_email.DATA_ID\n" +
                "\t\tWHERE\n" +
                "\t\t\t1 = 1\n" +
                "\t\tAND t_wrt_main.flage !=- 1\n" +
                "\t\tAND t_sys_email.E_TYPE = 1\n" +
                "\t\tAND t_sys_email_receiver.USER_ID IN (" + receiverIds + ")\n" +
                "\t\tAND t_sys_email.CREATE_USER_ID IN (" + senderIds + ")\n" +
                "\t\tAND t_sys_email.REPORT_DATE = '" + dateTime + "'\n" +
                "\t) AS email ON sysUser.userId=email.senderId\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tCOUNT(1) AS replyNum,\n" +
                "\t\tDATA_ID\n" +
                "\tFROM\n" +
                "\t\tt_sys_email_receiver\n" +
                "\tWHERE\n" +
                "\t\t1 = 1\n" +
                "\tAND REPLY_TIME IS NOT NULL\n" +
                "\tAND DATA_ID IN (\n" +
                "\t\tSELECT\n" +
                "\t\t\tt_wrt_main.ID\n" +
                "\t\tFROM\n" +
                "\t\t\tt_wrt_main\n" +
                "\t\tWHERE\n" +
                "\t\t\tt_wrt_main.USER_ID IN (" + senderIds + ")\n" +
                "\t\t AND t_sys_email_receiver.USER_ID IN (" + receiverIds + ") \n" +
                "\t\tAND t_wrt_main.REPORT_DATE = '" + dateTime + "'\n" +
                "\t)\n" +
                "\tGROUP BY\n" +
                "\t\tDATA_ID\n" +
                ") AS emailNum ON email.WId = emailNum.DATA_ID\n" +
                "LEFT JOIN t_sys_leave ON sysUser.userId = t_sys_leave.USER_ID\n" +
                "AND LEAVE_DATE = '" + dateTime + "'\n" +
                "LEFT JOIN t_sys_user AS a ON a.ID = email.receiverId\n" +
                "LEFT JOIN t_sys_user AS b ON b.ID = sysUser.userId\n" +
                "\tLEFT JOIN (\n" +
                "\t\tSELECT\n" +
                "\t\t\tt_sys_region.`NAME` AS regionName,\n" +
                "\t\t\tt_sys_region.`ID` AS regionId,\n" +
                "\t\t\tt_sys_region.SORT_NO AS regionSortNo,\n" +
                "\t\t\tt_sys_company.SORT_NO AS companySortNo,\n" +
                "\t\t\tt_sys_region.COMPANY_ID AS companyId,\n" +
                "\t\t\tt_sys_user_2_region.USER_ID AS userId\n" +
                "\t\tFROM\n" +
                "\t\t\tt_sys_user_2_region\n" +
                "\t\tLEFT JOIN t_sys_region ON t_sys_region.ID = t_sys_user_2_region.REGION_ID\n" +
                "\t\tLEFT JOIN t_sys_company ON t_sys_region.company_id = t_sys_company.id\n" +
                "\t\tWHERE\n" +
                "\t\t\t1 = 1\n" +
                "\t\tAND t_sys_user_2_region.user_id in (" + senderIds + ")\n" +
                "\t\tAND t_sys_company.DEL_FLAG = 0\n" +
                "\t\tAND t_sys_region.DEL_FLAG = 0\n" +
                "\t\tAND t_sys_region.PARENT_ID = 0\n" +
                "\t) AS sysRegion  ON sysUser.userId = sysRegion.userId\n" +
                "ORDER BY\n" +
                "\tsysRegion.companySortNo,sysRegion.regionSortNo,email.receiverId\n");
        return super.queryForJsonList(sql.toString());
    }

    //人做主表
    public List<JSONObject> getEmailForSupport2(String senderIds, String receiverIds, String dateTime) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "sysUser.companyName as regionName,\n" +
                "sysUser.companyId,\n" +
                "\ta.`NAME` AS readerName,\n" +
                "\ta.`ID` AS readerId,\n" +
                "\tb.`NAME` AS userName,\n" +
                "\tb.`ID` AS userId,\n" +
                "\temail.WId,\n" +
                "\temail.emailId,\n" +
                "\tCASE\n" +
                "WHEN email.READER = '1' THEN\n" +
                "\t'已读'\n" +
                "ELSE\n" +
                "\t'未读'\n" +
                "END AS reader,\n" +
                " CASE\n" +
                "WHEN (\n" +
                "\temail.TITLE IS NOT NULL || email.TITLE != ''\n" +
                ") THEN\n" +
                "\t'已发送'\n" +
                "ELSE\n" +
                "\t'未发送'\n" +
                "END AS titleSend,\n" +
                " CASE\n" +
                "WHEN (\n" +
                "\tt_sys_leave.id IS NOT NULL \n" +
                ") THEN\n" +
                "\t'请假'\n" +
                "WHEN (\n" +
                "\tt_sys_sign_in.id IS  NULL \n" +
                ") THEN\n" +
                "\t'缺勤'\n" +
                "ELSE\n" +
                "\t'出勤'\n" +
                "END AS leaveDate,\n" +
                " IFNULL(emailNum.replyNum, '0') AS replyNum\n" +
                "FROM\n" +
                " (\n" +
                "\tSELECT\n" +
                "\t\tv_sys_user.ID AS userId,\n" +
                "\t\tv_sys_user.`name` AS userName,\n" +
                "\t\tv_sys_user.companyName,\n" +
                "\t\tv_sys_user.companyId,\n" +
                "\t\tv_sys_user.companySortNo\n" +
                "\tFROM\n" +
                "\t\t\tv_sys_user\n" +
                "\tWHERE\n" +
                "\t\t1 = 1\n" +
                "\tAND v_sys_user.id IN (" + senderIds + ")\n" +
                "GROUP BY v_sys_user.id,v_sys_user.companyId\n" +
                ") AS sysUser\n" +
                "LEFT JOIN\n" +
                "(\n" +
                "\t\tSELECT\n" +
                "\t\t\tt_sys_email.CREATE_USER_ID AS senderId,\n" +
                "\t\t\tt_sys_email_receiver.USER_ID AS receiverId,\n" +
                "\t\t\tt_wrt_main.ID AS id,\n" +
                "\t\t\tt_sys_email_receiver.READER,\n" +
                "\t\t\tt_wrt_main.TITLE,\n" +
                "\t\t\tt_sys_email.ID AS emailId,\n" +
                "\t\t\tt_sys_email.DATA_ID AS WId\n" +
                "\t\tFROM\n" +
                "\t\t\tt_sys_email_receiver\n" +
                "\t\tLEFT JOIN t_sys_email ON t_sys_email_receiver.EMAIL_ID = t_sys_email.ID\n" +
                "\t\tLEFT JOIN t_wrt_main ON t_wrt_main.ID = t_sys_email.DATA_ID\n" +
                "\t\tWHERE\n" +
                "\t\t\t1 = 1\n" +
                "\t\tAND t_wrt_main.flage !=- 1\n" +
                "\t\tAND t_sys_email.E_TYPE = 1\n" +
                "\t\tAND t_sys_email_receiver.USER_ID IN (" + receiverIds + ")\n" +
                "\t\tAND t_sys_email.CREATE_USER_ID IN (" + senderIds + ")\n" +
                "\t\tAND t_sys_email.REPORT_DATE = '" + dateTime + "'\n" +
                "\t) AS email ON sysUser.userId=email.senderId\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tCOUNT(1) AS replyNum,\n" +
                "\t\tDATA_ID\n" +
                "\tFROM\n" +
                "\t\tt_sys_email_receiver\n" +
                "\tWHERE\n" +
                "\t\t1 = 1\n" +
                "\tAND REPLY_TIME IS NOT NULL\n" +
                "\tAND DATA_ID IN (\n" +
                "\t\tSELECT\n" +
                "\t\t\tt_wrt_main.ID\n" +
                "\t\tFROM\n" +
                "\t\t\tt_wrt_main\n" +
                "\t\tWHERE\n" +
                "\t\t\tt_wrt_main.USER_ID IN (" + senderIds + ")\n" +
                "\t\t AND t_sys_email_receiver.USER_ID IN (" + receiverIds + ") \n" +
                "\t\tAND t_wrt_main.REPORT_DATE = '" + dateTime + "'\n" +
                "\t)\n" +
                "\tGROUP BY\n" +
                "\t\tDATA_ID\n" +
                ") AS emailNum ON email.WId = emailNum.DATA_ID\n" +
                "LEFT JOIN t_sys_leave ON sysUser.userId = t_sys_leave.USER_ID\n" +
                "AND LEAVE_DATE = '" + dateTime + "'\n" +
                "LEFT JOIN t_sys_sign_in ON sysUser.userId = t_sys_sign_in.USER_ID\n" +
                "AND SIGN_DATE = '" + dateTime + "'\n" +
                "LEFT JOIN t_sys_user AS a ON a.ID = email.receiverId\n" +
                "LEFT JOIN t_sys_user AS b ON b.ID = sysUser.userId\n" +
                "\n" +
                "ORDER BY\n" +
                "\tsysUser.companySortNo,email.receiverId\n\n");
        return super.queryForJsonList(sql.toString());
    }

    //人做主表
    public JSONObject getEmailForSupport1(String senderIds, String receiverIds, String dateTime) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\temail.WId,\n" +
                "\temail.emailId,\n" +
                "\tCASE\n" +
                "WHEN email.READER = '1' THEN\n" +
                "\t'已读'\n" +
                "ELSE\n" +
                "\t'未读'\n" +
                "END AS reader,\n" +
                " CASE\n" +
                "WHEN (\n" +
                "\temail.TITLE IS NOT NULL || email.TITLE != ''\n" +
                ") THEN\n" +
                "\t'已发送'\n" +
                "ELSE\n" +
                "\t'未发送'\n" +
                "END AS titleSend,\n" +
                " CASE\n" +
                "WHEN (\n" +
                "\tt_sys_leave.id IS NOT NULL \n" +
                ") THEN\n" +
                "\t'请假'\n" +
                "WHEN (\n" +
                "\tt_sys_sign_in.id IS  NULL \n" +
                ") THEN\n" +
                "\t'缺勤'\n" +
                "ELSE\n" +
                "\t'出勤'\n" +
                "END AS leaveDate,\n" +
                " IFNULL(emailNum.replyNum, '0') AS replyNum\n" +
                "FROM\n" +
                "(\n" +
                "SELECT t_sys_user.ID as userId\n" +
                "FROM t_sys_user\n" +
                "WHERE ID=" + senderIds + "\n" +
                ") as sysUser\n" +
                "left join (\n" +
                "\tSELECT\n" +
                "\t\tt_sys_email.CREATE_USER_ID AS senderId,\n" +
                "\t\tt_sys_email_receiver.USER_ID AS receiverId,\n" +
                "\t\tt_wrt_main.ID AS id,\n" +
                "\t\tt_sys_email_receiver.READER,\n" +
                "\t\tt_wrt_main.TITLE,\n" +
                "\t\tt_sys_email.ID AS emailId,\n" +
                "\t\tt_sys_email.DATA_ID AS WId\n" +
                "\tFROM\n" +
                "\t\tt_sys_email_receiver\n" +
                "\tLEFT JOIN t_sys_email ON t_sys_email_receiver.EMAIL_ID = t_sys_email.ID\n" +
                "\tLEFT JOIN t_wrt_main ON t_wrt_main.ID = t_sys_email.DATA_ID\n" +
                "\tWHERE\n" +
                "\t\t1 = 1\n" +
                "\tAND t_wrt_main.flage !=- 1\n" +
                "\tAND t_sys_email.E_TYPE = 1\n" +
                "\tAND t_sys_email_receiver.USER_ID = '" + receiverIds + "'\n" +
                "\tAND t_sys_email.CREATE_USER_ID IN (" + senderIds + ")\n" +
                "\tAND t_sys_email.REPORT_DATE = '" + dateTime + "'\n" +
                ") AS email  ON sysUser.userId=email.senderId\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tCOUNT(1) AS replyNum,\n" +
                "\t\tDATA_ID\n" +
                "\tFROM\n" +
                "\t\tt_sys_email_receiver\n" +
                "\tWHERE\n" +
                "\t\t1 = 1\n" +
                "\tAND REPLY_TIME IS NOT NULL\n" +
                "\tAND DATA_ID IN (\n" +
                "\t\tSELECT\n" +
                "\t\t\tt_wrt_main.ID\n" +
                "\t\tFROM\n" +
                "\t\t\tt_wrt_main\n" +
                "\t\tWHERE\n" +
                "\t\t\tt_wrt_main.USER_ID IN (" + senderIds + ")\n" +
                "\t\tAND t_sys_email_receiver.USER_ID = '" + receiverIds + "'\n" +
                "\t\tAND t_wrt_main.REPORT_DATE = '" + dateTime + "'\n" +
                "\t)\n" +
                "\tGROUP BY\n" +
                "\t\tDATA_ID\n" +
                ") AS emailNum ON email.WId = emailNum.DATA_ID\n" +
                "LEFT JOIN t_sys_leave ON  t_sys_leave.USER_ID=sysUser.userId\n" +
                "AND LEAVE_DATE = '" + dateTime + "'\n" +
                "LEFT JOIN t_sys_sign_in ON sysUser.userId = t_sys_sign_in.USER_ID\n" +
                "AND SIGN_DATE = '" + dateTime + "'\n" +
                " GROUP BY\n" +
                " email.receiverId\n");
        List<JSONObject> result = super.queryForJsonList(sql.toString());
        if (null != result && result.size() > 0) {
            return result.get(0);
        } else {
            return null;
        }
    }

    public List<JSONObject> getCalendar(String userId, String monthTime, String afterMonth) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\tLEAVE_DATE AS leaveDate,\n" +
                "\tREMARK AS remark\n" +
                "FROM\n" +
                "\tt_sys_leave\n" +
                "WHERE\n" +
                "\tUSER_ID = '" + userId + "'\n" +
                "AND (LEAVE_DATE LIKE '%" + monthTime + "%' or LEAVE_DATE LIKE '%" + afterMonth + "%')\n" +
                "ORDER BY LEAVE_DATE\n");
        return super.queryForJsonList(sql.toString());
    }

    public List<JSONObject> getSginForQYZCZSQ(String managerId, String dateTime) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\tregion.regionName,\n" +
                "\tmanager.managerId,\n" +
                "\tmanager.managerName,\n" +
                "\tsign_leave.createTime,\n" +
                "\tIFNULL(sign_leave.signState, 2) AS signState,\n" +
                "\tsign_leave.address\n" +
                "FROM\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\tt_sys_region.ID AS regionId,\n" +
                "\t\t\tt_sys_region.`NAME` AS regionName,\n" +
                "\t\t\tt_sys_region.SORT_NO AS sortNo,\n" +
                "\t\t\tt_sys_company.SORT_NO AS companySort\n" +
                "\t\tFROM\n" +
                "\t\t\tt_sys_region\n" +
                "\t\tLEFT JOIN t_sys_user_2_region ON t_sys_region.ID = t_sys_user_2_region.REGION_ID\n" +
                "\t\tLEFT JOIN t_sys_user ON t_sys_user_2_region.USER_ID = t_sys_user.ID\n" +
                "\t\tLEFT JOIN t_sys_company ON t_sys_region.COMPANY_ID = t_sys_company.ID\n" +
                "\t\tWHERE\n" +
                "\t\t\t1 = 1\n" +
                "\t\tAND t_sys_user.ID = '" + managerId + "'\n" +
                "\t\tAND t_sys_region.PARENT_ID = '0'\n" +
                "\t\tAND t_sys_region.DEL_FLAG = '0'\n" +
                "\t) AS region\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tt_sys_user.ID AS managerId,\n" +
                "\t\tt_sys_user.`NAME` AS managerName,\n" +
                "\t\tt_sys_user_2_region.REGION_ID AS regionId,\n" +
                "\t\tt_sys_role_name.SORT_NO AS roleNameSortNo,\n" +
                "\t\tt_sys_user.SORT_NO AS userSortNo\n" +
                "\tFROM\n" +
                "\t\tt_sys_user\n" +
                "\tLEFT JOIN t_sys_user_2_region ON t_sys_user.ID = t_sys_user_2_region.USER_ID\n" +
                "\tLEFT JOIN t_sys_role ON t_sys_user.ROLE_ID = t_sys_role.ID\n" +
                "\tLEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID = t_sys_role_name.ID\n" +
                "\tWHERE\n" +
                "\t\tt_sys_role_name.`NAME` IN (\n" +
                "\t\t\t'区域支持',\n" +
                "\t\t\t'销售支持',\n" +
                "\t\t\t'办事处支持'\n" +
                "\t\t)\n" +
                "\tAND t_sys_user.JOB_STATE != 1\n" +
                ") AS manager ON manager.regionId = region.regionId\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tt_sys_user.ID AS managerId,\n" +
                "\t\tt_sys_user.`NAME` AS managerName,\n" +
                "\t\tt_sys_user_2_region.REGION_ID AS regionId,\n" +
                "\t\tt_sys_sign_in.CREATE_TIME AS createTime,\n" +
                "\t\tt_sys_sign_in.ADDRESS AS address,\n" +
                "\t\t'0' AS signState\n" +
                "\tFROM\n" +
                "\t\tt_sys_sign_in\n" +
                "\tLEFT JOIN t_sys_user_2_region ON t_sys_sign_in.USER_ID = t_sys_user_2_region.USER_ID\n" +
                "\tLEFT JOIN t_sys_user ON t_sys_sign_in.USER_ID = t_sys_user.ID\n" +
                "\tLEFT JOIN t_sys_role ON t_sys_user.ROLE_ID = t_sys_role.ID\n" +
                "\tLEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID = t_sys_role_name.ID\n" +
                "\tWHERE\n" +
                "\t\tt_sys_sign_in.SIGN_DATE = '" + dateTime + "'\n" +
                "\tAND t_sys_role_name.`NAME` IN (\n" +
                "\t\t'区域支持',\n" +
                "\t\t'销售支持',\n" +
                "\t\t'办事处支持'\n" +
                "\t)\n" +
                "\tUNION ALL\n" +
                "\t\tSELECT\n" +
                "\t\t\tt_sys_user.ID AS managerId,\n" +
                "\t\t\tt_sys_user.`NAME` AS managerName,\n" +
                "\t\t\tt_sys_user_2_region.REGION_ID AS regionId,\n" +
                "\t\t\tt_sys_leave.CREATE_TIME AS createTime,\n" +
                "\t\t\tt_sys_leave.REMARK AS address,\n" +
                "\t\t\t'1' AS signState\n" +
                "\t\tFROM\n" +
                "\t\t\tt_sys_leave\n" +
                "\t\tLEFT JOIN t_sys_user_2_region ON t_sys_leave.USER_ID = t_sys_user_2_region.USER_ID\n" +
                "\t\tLEFT JOIN t_sys_user ON t_sys_leave.USER_ID = t_sys_user.ID\n" +
                "\t\tLEFT JOIN t_sys_role ON t_sys_user.ROLE_ID = t_sys_role.ID\n" +
                "\t\tLEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID = t_sys_role_name.ID\n" +
                "\t\tWHERE\n" +
                "\t\t\tt_sys_leave.LEAVE_DATE = '" + dateTime + "'\n" +
                "\t\tAND t_sys_role_name.`NAME` IN (\n" +
                "\t\t\t'区域支持',\n" +
                "\t\t\t'销售支持',\n" +
                "\t\t\t'办事处支持'\n" +
                "\t\t)\n" +
                ") AS sign_leave ON region.regionId = sign_leave.regionId\n" +
                "AND manager.managerId = sign_leave.managerId\n" +
                "ORDER BY\n" +
                "\tmanager.roleNameSortNo,\n" +
                "\tmanager.managerId,\n" +
                "\tmanager.userSortNo");
        return super.queryForJsonList(sql.toString());
    }

    public List<JSONObject> getSginForXSZJZSQ(String companyId, String dateTime) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\tmanager.managerId,\n" +
                "\tmanager.managerName,\n" +
                "\tmanager.companyName,\n" +
                "\tsign_leave.createTime,\n" +
                "\tIFNULL(sign_leave.signState, 2) AS signState,\n" +
                "\tsign_leave.address\n" +
                "FROM\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\tid AS managerId,\n" +
                "\t\t\t`name` AS managerName,\n" +
                "\t\t\tcompanyId,\n" +
                "\t\t\tcompanyName,\n" +
                "\t\t\tcompanySortNo,\n" +
                "\t\t\tdepartSortNo,\n" +
                "\t\t\troleSortNo,\n" +
                "\t\t\tuserSortNo\n" +
                "\t\tFROM\n" +
                "\t\t\tv_sys_user\n" +
                "\t\tWHERE\n" +
                "\t\t\tcompanyId = '" + companyId + "'\n" +
                "\t\tAND jobState != 1\n" +
                "\t\tAND roleName = '销售总监'\n" +
                "\t) AS manager\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tt_sys_sign_in.USER_ID AS managerId,\n" +
                "\t\tt_sys_sign_in.CREATE_TIME AS createTime,\n" +
                "\t\tt_sys_sign_in.ADDRESS AS address,\n" +
                "\t\t'0' AS signState\n" +
                "\tFROM\n" +
                "\t\tt_sys_sign_in\n" +
                "\tWHERE\n" +
                "\tt_sys_sign_in.SIGN_DATE='" + dateTime + "'\n" +
                "\tUNION ALL\n" +
                "\t\tSELECT\n" +
                "\t\t\tt_sys_leave.USER_ID AS managerId,\n" +
                "\t\t\tt_sys_leave.CREATE_TIME AS createTime,\n" +
                "\t\t\tt_sys_leave.REMARK AS address,\n" +
                "\t\t\t'1' AS signState\n" +
                "\t\tFROM\n" +
                "\t\t\tt_sys_leave\n" +
                "\t\tWHERE\n" +
                "\t\t\tt_sys_leave.LEAVE_DATE = '" + dateTime + "'\n" +
                ") AS sign_leave ON manager.managerId = sign_leave.managerId\n" +
                "ORDER BY\n" +
                "\tmanager.companySortNo,\n" +
                "\tmanager.departSortNo,\n" +
                "\tmanager.roleSortNo,\n" +
                "\tmanager.userSortNo");
        return super.queryForJsonList(sql.toString());
    }

    public List<JSONObject> getSginForTOPZSQ(String managerId, String dateTime) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\tregion.regionName,\n" +
                "\tmanager.managerId,\n" +
                "\tmanager.managerName,\n" +
                "\tsign_leave.createTime,\n" +
                "\tIFNULL(sign_leave.signState, 2) AS signState,\n" +
                "\tsign_leave.address\n" +
                "FROM\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\tt_sys_region.ID AS regionId,\n" +
                "\t\t\tt_sys_region.`NAME` AS regionName,\n" +
                "\t\t\tt_sys_region.SORT_NO AS sortNo,\n" +
                "\t\t\tt_sys_company.SORT_NO AS companySort\n" +
                "\t\tFROM\n" +
                "\t\t\tt_sys_region\n" +
                "\t\tLEFT JOIN t_sys_user_2_region ON t_sys_region.ID = t_sys_user_2_region.REGION_ID\n" +
                "\t\tLEFT JOIN t_sys_user ON t_sys_user_2_region.USER_ID = t_sys_user.ID\n" +
                "\t\tLEFT JOIN t_sys_company ON t_sys_region.COMPANY_ID = t_sys_company.ID\n" +
                "\t\tWHERE\n" +
                "\t\t\t1 = 1\n" +
                "\t\tAND t_sys_user.ID = '" + managerId + "'\n" +
                "\t\tAND t_sys_region.PARENT_ID = '0'\n" +
                "\t\tAND t_sys_region.DEL_FLAG = '0'\n" +
                "\t) AS region\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tt_sys_user.ID AS managerId,\n" +
                "\t\tt_sys_user.`NAME` AS managerName,\n" +
                "\t\tt_sys_user_2_region.REGION_ID AS regionId\n" +
                "\tFROM\n" +
                "\t\tt_sys_user\n" +
                "\tLEFT JOIN t_sys_user_2_region ON t_sys_user.ID = t_sys_user_2_region.USER_ID\n" +
                "\tLEFT JOIN t_sys_role ON t_sys_user.ROLE_ID = t_sys_role.ID\n" +
                "\tLEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID = t_sys_role_name.ID\n" +
                "\tWHERE\n" +
                "\t\tt_sys_role_name.`NAME` = '区域总监'\n" +
                "\tAND t_sys_user.JOB_STATE != 1\n" +
                ") AS manager ON manager.regionId = region.regionId\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tt_sys_user.ID AS managerId,\n" +
                "\t\tt_sys_user.`NAME` AS managerName,\n" +
                "\t\tt_sys_user_2_region.REGION_ID AS regionId,\n" +
                "\t\tt_sys_sign_in.CREATE_TIME AS createTime,\n" +
                "\t\tt_sys_sign_in.ADDRESS AS address,\n" +
                "\t\t'0' AS signState\n" +
                "\tFROM\n" +
                "\t\tt_sys_sign_in\n" +
                "\tLEFT JOIN t_sys_user_2_region ON t_sys_sign_in.USER_ID = t_sys_user_2_region.USER_ID\n" +
                "\tLEFT JOIN t_sys_user ON t_sys_sign_in.USER_ID = t_sys_user.ID\n" +
                "\tLEFT JOIN t_sys_role ON t_sys_user.ROLE_ID = t_sys_role.ID\n" +
                "\tLEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID = t_sys_role_name.ID\n" +
                "\tWHERE\n" +
                "\t\tt_sys_sign_in.SIGN_DATE = '" + dateTime + "'\n" +
                "\tAND t_sys_role_name.`NAME` = '区域总监'\n" +
                "\tUNION ALL\n" +
                "\t\tSELECT\n" +
                "\t\t\tt_sys_user.ID AS managerId,\n" +
                "\t\t\tt_sys_user.`NAME` AS managerName,\n" +
                "\t\t\tt_sys_user_2_region.REGION_ID AS regionId,\n" +
                "\t\t\tt_sys_leave.CREATE_TIME AS createTime,\n" +
                "\t\t\tt_sys_leave.REMARK AS address,\n" +
                "\t\t\t'1' AS signState\n" +
                "\t\tFROM\n" +
                "\t\t\tt_sys_leave\n" +
                "\t\tLEFT JOIN t_sys_user_2_region ON t_sys_leave.USER_ID = t_sys_user_2_region.USER_ID\n" +
                "\t\tLEFT JOIN t_sys_user ON t_sys_leave.USER_ID = t_sys_user.ID\n" +
                "\t\tLEFT JOIN t_sys_role ON t_sys_user.ROLE_ID = t_sys_role.ID\n" +
                "\t\tLEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID = t_sys_role_name.ID\n" +
                "\t\tWHERE\n" +
                "\t\t\tt_sys_leave.LEAVE_DATE = '" + dateTime + "'\n" +
                "\t\tAND t_sys_role_name.`NAME` = '区域总监'\n" +
                ") AS sign_leave ON region.regionId = sign_leave.regionId\n" +
                " WHERE manager.managerId is not NULL ORDER BY\n" +
                "\tregion.companySort,\n" +
                "\tregion.sortNo");
        return super.queryForJsonList(sql.toString());
    }

    public List<JSONObject> getBigRegionForZSQ(String userId) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\tt_sys_region.ID AS regionId,\n" +
                "\tt_sys_region.`NAME` AS regionName\n" +
                "FROM\n" +
                "\tt_sys_region\n" +
                "LEFT JOIN t_sys_user_2_region ON t_sys_region.ID = t_sys_user_2_region.REGION_ID \n" +
                "LEFT JOIN t_sys_company ON t_sys_region.COMPANY_ID = t_sys_company.ID\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "AND t_sys_user_2_region.USER_ID = '" + userId + "'\n" +
                "AND t_sys_region.PARENT_ID = '0'\n" +
                "AND t_sys_region.DEL_FLAG = '0'\n" +
                "ORDER BY\n" +
                "\tt_sys_company.SORT_NO,\n" +
                "\tt_sys_region.SORT_NO");
        return super.queryForJsonList(sql.toString());
    }

    public List<JSONObject> getBigRegionForZSQ(String userId, String dateTime) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT \n" +
                "region.regionName,\n" +
                "region.bigRegionId,\n" +
                "sign_leave.managerId,\n" +
                "sign_leave.managerName,\n" +
                "sign_leave.createTime,\n" +
                "IFNULL(sign_leave.signState,2) AS signState,\n" +
                "sign_leave.address FROM (\n" +
                "select t_sys_region.ID AS regionId,t_sys_region.`NAME` AS regionName,bigRegion.ID AS bigRegionId,\n" +
                "bigRegion.SORT_NO AS bigSortNo,t_sys_region.SORT_NO AS sortNo,t_sys_company.SORT_NO AS companySortNo\n" +
                " from t_sys_region \n" +
                "LEFT JOIN t_sys_user_2_region ON t_sys_region.ID=t_sys_user_2_region.REGION_ID\n" +
                "LEFT JOIN t_sys_region AS bigRegion ON t_sys_region.PARENT_ID=bigRegion.ID\n" +
                "LEFT JOIN t_sys_company ON t_sys_region.COMPANY_ID=t_sys_company.ID\n" +
                "WHERE \n" +
                "1=1\n" +
                "AND t_sys_user_2_region.USER_ID='" + userId + "'\n" +
                "AND t_sys_region.PARENT_ID!='0'\n" +
                "AND t_sys_region.DEL_FLAG='0')as region\n" +
                "LEFT JOIN(\n" +
                "SELECT t_sys_user.ID AS managerId,t_sys_user.`NAME` AS managerName,\n" +
                "t_sys_user_2_region.REGION_ID AS regionId from t_sys_user\n" +
                "LEFT JOIN t_sys_user_2_region ON t_sys_user.ID=t_sys_user_2_region.USER_ID\n" +
                "LEFT JOIN t_sys_role ON t_sys_user.ROLE_ID=t_sys_role.ID\n" +
                "LEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID=t_sys_role_name.ID\n" +
                "\n" +
                "WHERE (t_sys_role_name.`NAME`='区域经理' OR t_sys_role_name.`NAME`='区域组长')\n" +
                "AND t_sys_user.JOB_STATE!=1\n" +
                ") AS manager ON manager.regionId=region.regionId\n" +
                "LEFT JOIN (\n" +
                "SELECT t_sys_user.ID AS managerId,t_sys_user.`NAME` AS managerName,t_sys_user_2_region.REGION_ID AS regionId,\n" +
                "t_sys_sign_in.CREATE_TIME AS createTime,\n" +
                "t_sys_sign_in.ADDRESS AS address,\n" +
                "'0' AS signState from t_sys_sign_in\n" +
                "LEFT JOIN t_sys_user_2_region ON t_sys_sign_in.USER_ID=t_sys_user_2_region.USER_ID\n" +
                "LEFT JOIN t_sys_user ON t_sys_sign_in.USER_ID=t_sys_user.ID\n" +
                "LEFT JOIN t_sys_role ON t_sys_user.ROLE_ID=t_sys_role.ID\n" +
                "LEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID=t_sys_role_name.ID\n" +
                "WHERE t_sys_sign_in.SIGN_DATE='" + dateTime + "'\n" +
                "AND (t_sys_role_name.`NAME`='区域经理' OR t_sys_role_name.`NAME`='区域组长')\n" +
                "UNION ALL\n" +
                "SELECT t_sys_user.ID AS managerId,t_sys_user.`NAME` AS managerName,t_sys_user_2_region.REGION_ID AS regionId,\n" +
                "t_sys_leave.CREATE_TIME AS createTime,\n" +
                "t_sys_leave.REMARK AS address,\n" +
                "'1' AS signState from t_sys_leave\n" +
                "LEFT JOIN t_sys_user_2_region ON t_sys_leave.USER_ID=t_sys_user_2_region.USER_ID\n" +
                "LEFT JOIN t_sys_user ON t_sys_leave.USER_ID=t_sys_user.ID\n" +
                "LEFT JOIN t_sys_role ON t_sys_user.ROLE_ID=t_sys_role.ID\n" +
                "LEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID=t_sys_role_name.ID\n" +
                "WHERE t_sys_leave.LEAVE_DATE='" + dateTime + "'\n" +
                "AND (t_sys_role_name.`NAME`='区域经理' OR t_sys_role_name.`NAME`='区域组长')\n" +
                ")AS sign_leave ON region.regionId=sign_leave.regionId\n" +
                "ORDER BY region.companySortNo,region.bigSortNo,region.sortNo\n\n\n");
        return super.queryForJsonList(sql.toString());
    }

    //上级作阅读人(大区经理)区域做主表
    public List<JSONObject> getEmailForZSQ(String userId, String senderIds, String receiverIds, String dateTime) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT * FROM\n" +
                "(\n" +
                "SELECT\n" +
                "\tsysRegion.regionName,\n" +
                "\tsysRegion.companySortNo,\n" +
                "\tsysRegion.regionSortNo,\n" +
                "\tsysRegion.regionId,\n" +
                "\tsysRegion.companyId,\n" +
                "\tsysUser.userId,\n" +
                "\ta.`NAME` AS readerName,\n" +
                "\ta.`ID` AS readerId,\n" +
                "\tb.PARENT_ID AS senderParentId,\n" +
                "\tsysUser.userName,\n" +
                "\temail.WId,\n" +
                "\temail.emailId,\n" +
                "\tCASE\n" +
                "WHEN email.READER = '1' THEN\n" +
                "\t'已读'\n" +
                "ELSE\n" +
                "\t'未读'\n" +
                "END AS reader,\n" +
                " CASE\n" +
                "WHEN (\n" +
                "\temail.TITLE IS NOT NULL || email.TITLE != ''\n" +
                ") THEN\n" +
                "\t'已发送'\n" +
                "ELSE\n" +
                "\t'未发送'\n" +
                "END AS titleSend,\n" +
                " CASE\n" +
                "WHEN (\n" +
                "\tt_sys_leave.id IS NOT NULL \n" +
                ") THEN\n" +
                "\t'请假'\n" +
                "WHEN (\n" +
                "\tt_sys_sign_in.id IS  NULL \n" +
                ") THEN\n" +
                "\t'缺勤'\n" +
                "ELSE\n" +
                "\t'出勤'\n" +
                "END AS leaveDate,\n" +
                " IFNULL(emailNum.replyNum, '0') AS replyNum\n" +
                "FROM\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\tt_sys_region.`NAME` AS regionName,\n" +
                "\t\t\tt_sys_region.`ID` AS regionId,\n" +
                "\t\t\tt_sys_region.SORT_NO AS regionSortNo,\n" +
                "\t\t\tt_sys_company.SORT_NO AS companySortNo,\n" +
                "\t\t\tt_sys_region.COMPANY_ID AS companyId\n" +
                "\t\tFROM\n" +
                "\t\t\tt_sys_user_2_region\n" +
                "\t\tLEFT JOIN t_sys_region ON t_sys_region.id = t_sys_user_2_region.REGION_ID\n" +
                "\t\tLEFT JOIN t_sys_company ON t_sys_company.ID = t_sys_region.COMPANY_ID\n" +
                "\t\tAND t_sys_company.DEL_FLAG = 0\n" +
                "\t\tWHERE\n" +
                "\t\t\tt_sys_user_2_region.USER_ID = '" + userId + "'\n" +
                "\t\tAND t_sys_region.DEL_FLAG = 0\n" +
                "\t\tAND t_sys_region.PARENT_ID = 0\n" +
                "\t) AS sysRegion\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tt_sys_user.ID AS userId,\n" +
                "\t\tt_sys_depart.`NAME` AS departName,\n" +
                "\t\tt_sys_user.`NAME` AS userName,\n" +
                "\t\tt_sys_company.id AS companyId\n" +
                "\tFROM\n" +
                "\t\tt_sys_user\n" +
                "\tLEFT JOIN t_sys_role ON t_sys_role.ID=t_sys_user.ROLE_ID\n" +
                "\tLEFT JOIN t_sys_depart ON t_sys_depart.ID = t_sys_role.DEPART_ID\n" +
                "\tLEFT JOIN t_sys_company ON t_sys_company.ID = t_sys_depart.COMPANY_ID\n" +
                "\tWHERE\n" +
                "\t\t1 = 1\n" +
                "\tAND t_sys_company.DEL_FLAG = 0\n" +
                "\tAND t_sys_depart.DEL_FLAG = 0\n" +
                "\tAND t_sys_role.DEL_FLAG = 0\n" +
                "\tAND t_sys_user.ID IN (" + senderIds + ")\n" +
                ") AS sysUser ON sysUser.departName = sysRegion.regionName\n" +
                "AND sysRegion.companyId = sysUser.companyId\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tt_sys_email.CREATE_USER_ID AS senderId,\n" +
                "\t\tt_sys_email_receiver.USER_ID AS receiverId,\n" +
                "\t\tt_wrt_main.ID AS id,\n" +
                "\t\tt_sys_email_receiver.READER,\n" +
                "\t\tt_wrt_main.TITLE,\n" +
                "\t\tt_sys_email.ID AS emailId,\n" +
                "\t\tt_sys_email.DATA_ID AS WId\n" +
                "\tFROM\n" +
                "\t\tt_sys_email_receiver\n" +
                "\tLEFT JOIN t_sys_email ON t_sys_email_receiver.EMAIL_ID = t_sys_email.ID\n" +
                "\tLEFT JOIN t_wrt_main ON t_wrt_main.ID = t_sys_email.DATA_ID\n" +
                "\tWHERE\n" +
                "\t\t1 = 1\n" +
                "\tAND t_wrt_main.flage !=- 1\n" +
                "\tAND t_sys_email.E_TYPE = 1\n" +
                "\tAND t_sys_email_receiver.USER_ID IN (" + receiverIds + ")\n" +
                "\tAND t_sys_email.CREATE_USER_ID IN (" + senderIds + ")\n" +
                "\tAND t_sys_email.REPORT_DATE = '" + dateTime + "'\n" +
                ") AS email ON email.senderId = sysUser.userId\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tCOUNT(1) AS replyNum,\n" +
                "\t\tDATA_ID\n" +
                "\tFROM\n" +
                "\t\tt_sys_email_receiver\n" +
                "\tWHERE\n" +
                "\t\t1 = 1\n" +
                "\tAND REPLY_TIME IS NOT NULL\n" +
                "\tAND DATA_ID IN (\n" +
                "\t\tSELECT\n" +
                "\t\t\tt_wrt_main.ID\n" +
                "\t\tFROM\n" +
                "\t\t\tt_wrt_main\n" +
                "\t\tWHERE\n" +
                "\t\t\tt_wrt_main.USER_ID IN (" + senderIds + ")\n" +
                "\t\tAND t_wrt_main.REPORT_DATE = '" + dateTime + "'\n" +
                "\t\t AND t_sys_email_receiver.USER_ID IN (" + receiverIds + ") \n" +
                "\t)\n" +
                "\tGROUP BY\n" +
                "\t\tDATA_ID\n" +
                ") AS emailNum ON email.WId = emailNum.DATA_ID\n" +
                "LEFT JOIN t_sys_leave ON sysUser.userId = t_sys_leave.USER_ID\n" +
                "AND LEAVE_DATE = '" + dateTime + "'\n" +
                "LEFT JOIN t_sys_sign_in ON sysUser.userId = t_sys_sign_in.USER_ID\n" +
                "AND SIGN_DATE = '" + dateTime + "'\n" +
                "LEFT JOIN t_sys_user AS a ON a.ID = email.receiverId\n" +
                "LEFT JOIN t_sys_user AS b ON b.ID = email.senderId\n" +
                "ORDER BY\n" +
                "\tsysRegion.companySortNo,\n" +
                "\tsysRegion.regionSortNo,\n" +
                "\temail.receiverId) as obj\n" +
                "WHERE 1=1\n" +
                "AND (obj.senderParentId=obj.readerId OR obj.readerId IS null)\n" +
                "ORDER BY obj.companySortNo,\n" +
                "\tobj.regionSortNo,\n" +
                "\tobj.readerId\n");
        return super.queryForJsonList(sql.toString());
    }

    public List<JSONObject> getUserIdByRegionIdForPartTimeJob(String regionId, String roleName, String userIds) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\tv_sys_user.id AS userId\n" +
                "FROM\n" +
                "\tt_sys_user_2_region\n" +
                "LEFT JOIN t_sys_region ON t_sys_region.ID=t_sys_user_2_region.REGION_ID\n" +
                "LEFT JOIN v_sys_user ON v_sys_user.ID=t_sys_user_2_region.USER_ID AND t_sys_region.`NAME`=v_sys_user.departName\n" +
                "\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "AND t_sys_user_2_region.REGION_ID = '" + regionId + "'\n" +
                "AND v_sys_user.roleName='" + roleName + "'\n" +
                "AND v_sys_user.ID IN (" + userIds + ")\n" +
                "GROUP BY v_sys_user.id\n");
        return super.queryForJsonList(sql.toString());
    }

    public List<JSONObject> getSignForZZRegionZSQ(String managerId, String dateTime) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "region.regionName,\n" +
                "manager.managerId,\n" +
                "manager.managerName,\n" +
                "sign_leave.createTime,\n" +
                "IFNULL(sign_leave.signState,2) AS signState,\n" +
                "sign_leave.address\n" +
                " FROM (\n" +
                "select t_sys_region.ID AS regionId,t_sys_region.`NAME` AS regionName,t_sys_region.SORT_NO AS sortNo,t_sys_company.SORT_NO AS companySortNo\n" +
                " from t_sys_region \n" +
                "LEFT JOIN t_sys_user_2_region ON t_sys_region.ID=t_sys_user_2_region.REGION_ID\n" +
                "LEFT JOIN t_sys_user ON t_sys_user_2_region.USER_ID=t_sys_user.ID\n" +
                "LEFT JOIN t_sys_company ON t_sys_region.COMPANY_ID=t_sys_company.ID\n" +
                "WHERE \n" +
                "1=1\n" +
                "AND t_sys_user.ID='" + managerId + "'\n" +
                "AND t_sys_region.PARENT_ID='0'\n" +
                "AND t_sys_region.DEL_FLAG='0'\n" +
                ")AS region\n" +
                "LEFT JOIN(\n" +
                "SELECT t_sys_user.ID AS managerId,t_sys_user.`NAME` AS managerName,\n" +
                "t_sys_user_2_region.REGION_ID AS regionId from t_sys_user\n" +
                "LEFT JOIN t_sys_user_2_region ON t_sys_user.ID=t_sys_user_2_region.USER_ID\n" +
                "LEFT JOIN t_sys_role ON t_sys_user.ROLE_ID=t_sys_role.ID\n" +
                "LEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID=t_sys_role_name.ID\n" +
                "\n" +
                "WHERE t_sys_role_name.`NAME`='区域组长'\n" +
                "AND t_sys_user.JOB_STATE!=1\n" +
                ") AS manager ON manager.regionId=region.regionId\n" +
                "LEFT JOIN (\n" +
                "SELECT t_sys_user.ID AS managerId,t_sys_user.`NAME` AS managerName,t_sys_user_2_region.REGION_ID AS regionId,\n" +
                "t_sys_sign_in.CREATE_TIME AS createTime,\n" +
                "t_sys_sign_in.ADDRESS AS address,\n" +
                "'0' AS signState from t_sys_sign_in\n" +
                "LEFT JOIN t_sys_user_2_region ON t_sys_sign_in.USER_ID=t_sys_user_2_region.USER_ID\n" +
                "LEFT JOIN t_sys_user ON t_sys_sign_in.USER_ID=t_sys_user.ID\n" +
                "LEFT JOIN t_sys_role ON t_sys_user.ROLE_ID=t_sys_role.ID\n" +
                "LEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID=t_sys_role_name.ID\n" +
                "WHERE t_sys_sign_in.SIGN_DATE='" + dateTime + "'\n" +
                "AND t_sys_role_name.`NAME`='区域组长'\n" +
                "UNION ALL\n" +
                "SELECT t_sys_user.ID AS managerId,t_sys_user.`NAME` AS managerName,t_sys_user_2_region.REGION_ID AS regionId,\n" +
                "t_sys_leave.CREATE_TIME AS createTime,\n" +
                "t_sys_leave.REMARK AS address,\n" +
                "'1' AS signState from t_sys_leave\n" +
                "LEFT JOIN t_sys_user_2_region ON t_sys_leave.USER_ID=t_sys_user_2_region.USER_ID\n" +
                "LEFT JOIN t_sys_user ON t_sys_leave.USER_ID=t_sys_user.ID\n" +
                "LEFT JOIN t_sys_role ON t_sys_user.ROLE_ID=t_sys_role.ID\n" +
                "LEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID=t_sys_role_name.ID\n" +
                "WHERE t_sys_leave.LEAVE_DATE='" + dateTime + "'\n" +
                "AND t_sys_role_name.`NAME`='区域组长'\n" +
                ")AS sign_leave ON region.regionId=sign_leave.regionId\n" +
                "ORDER BY region.companySortNo,region.sortNo");
        return super.queryForJsonList(sql.toString());
    }

    public List<JSONObject> getSginForZSQ(String managerId, String dateTime) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "region.regionName,\n" +
                "manager.managerId,\n" +
                "manager.managerName,\n" +
                "sign_leave.createTime,\n" +
                "IFNULL(sign_leave.signState,2) AS signState,\n" +
                "sign_leave.address\n" +
                " FROM (\n" +
                "select t_sys_region.ID AS regionId,t_sys_region.`NAME` AS regionName,t_sys_region.SORT_NO AS sortNo,t_sys_company.SORT_NO AS companySortNo\n" +
                " from t_sys_region \n" +
                "LEFT JOIN t_sys_user_2_region ON t_sys_region.ID=t_sys_user_2_region.REGION_ID\n" +
                "LEFT JOIN t_sys_user ON t_sys_user_2_region.USER_ID=t_sys_user.ID\n" +
                "LEFT JOIN t_sys_company ON t_sys_region.COMPANY_ID=t_sys_company.ID\n" +
                "WHERE \n" +
                "1=1\n" +
                "AND t_sys_user.ID='" + managerId + "'\n" +
                "AND t_sys_region.PARENT_ID='0'\n" +
                "AND t_sys_region.DEL_FLAG='0'\n" +
                ")AS region\n" +
                "LEFT JOIN(\n" +
                "SELECT t_sys_user.ID AS managerId,t_sys_user.`NAME` AS managerName,\n" +
                "t_sys_user_2_region.REGION_ID AS regionId from t_sys_user\n" +
                "LEFT JOIN t_sys_user_2_region ON t_sys_user.ID=t_sys_user_2_region.USER_ID\n" +
                "LEFT JOIN t_sys_role ON t_sys_user.ROLE_ID=t_sys_role.ID\n" +
                "LEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID=t_sys_role_name.ID\n" +
                "\n" +
                "WHERE t_sys_role_name.`NAME`='大区经理'\n" +
                "AND t_sys_user.JOB_STATE!=1\n" +
                ") AS manager ON manager.regionId=region.regionId\n" +
                "LEFT JOIN (\n" +
                "SELECT t_sys_user.ID AS managerId,t_sys_user.`NAME` AS managerName,t_sys_user_2_region.REGION_ID AS regionId,\n" +
                "t_sys_sign_in.CREATE_TIME AS createTime,\n" +
                "t_sys_sign_in.ADDRESS AS address,\n" +
                "'0' AS signState from t_sys_sign_in\n" +
                "LEFT JOIN t_sys_user_2_region ON t_sys_sign_in.USER_ID=t_sys_user_2_region.USER_ID\n" +
                "LEFT JOIN t_sys_user ON t_sys_sign_in.USER_ID=t_sys_user.ID\n" +
                "LEFT JOIN t_sys_role ON t_sys_user.ROLE_ID=t_sys_role.ID\n" +
                "LEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID=t_sys_role_name.ID\n" +
                "WHERE t_sys_sign_in.SIGN_DATE='" + dateTime + "'\n" +
                "AND t_sys_role_name.`NAME`='大区经理'\n" +
                "UNION ALL\n" +
                "SELECT t_sys_user.ID AS managerId,t_sys_user.`NAME` AS managerName,t_sys_user_2_region.REGION_ID AS regionId,\n" +
                "t_sys_leave.CREATE_TIME AS createTime,\n" +
                "t_sys_leave.REMARK AS address,\n" +
                "'1' AS signState from t_sys_leave\n" +
                "LEFT JOIN t_sys_user_2_region ON t_sys_leave.USER_ID=t_sys_user_2_region.USER_ID\n" +
                "LEFT JOIN t_sys_user ON t_sys_leave.USER_ID=t_sys_user.ID\n" +
                "LEFT JOIN t_sys_role ON t_sys_user.ROLE_ID=t_sys_role.ID\n" +
                "LEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID=t_sys_role_name.ID\n" +
                "WHERE t_sys_leave.LEAVE_DATE='" + dateTime + "'\n" +
                "AND t_sys_role_name.`NAME`='大区经理'\n" +
                ")AS sign_leave ON region.regionId=sign_leave.regionId\n" +
                "ORDER BY region.companySortNo,region.sortNo");
        return super.queryForJsonList(sql.toString());
    }

    public JSONObject getSignByMyself(String userId, String dateTime) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\tmanager.managerId,\n" +
                "\tmanager.managerName,\n" +
                "\tmanager.residence,\n" +
                "\tmanager.frontFlag,\n" +
                "\tsign.signInAlwaysDistance,\n" +
                "\tsign.signCreateTime,\n" +
                "\tsign.beginTime,\n" +
                "\tsign.latitude,\n" +
                "\tsign.longitude,\n" +
                "\tsign.endTime,\n" +
                "\tsign.address,\n" +
                "\tsign.signUrl,\n" +
                "\tsign.signOutUrl,\n" +
                "\tIFNULL(sign.signState, 2) AS signState\n" +
                "FROM\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\tt_sys_user.id AS managerId,\n" +
                "\t\t\tt_sys_user.`NAME` AS managerName,\n" +
                "\t\t\tt_sys_user.RESIDENCE AS residence,\n" +
                "\t\t\tt_sys_role_name.FRONT_FLAG AS frontFlag\n" +
                "\t\tFROM\n" +
                "\t\t\tt_sys_user\n" +
                "\t\tLEFT JOIN t_sys_role ON t_sys_user.ROLE_ID = t_sys_role.ID\n" +
                "\t\tLEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID = t_sys_role_name.ID\n" +
                "\t\tWHERE\n" +
                "\t\t\tt_sys_user.id = '" + userId + "'\n" +
                "\t) AS manager\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tt_sys_user.ID AS managerId,\n" +
                "\t\tt_sys_sign_in.CREATE_TIME AS signCreateTime,\n" +
                "\t\tLEFT (\n" +
                "\t\t\tt_sys_sign_in.CREATE_TIME,\n" +
                "\t\t\t10\n" +
                "\t\t) AS beginTime,\n" +
                "\t\tLEFT (\n" +
                "\t\t\tt_sys_sign_in.SIGN_OUT_TIME,\n" +
                "\t\t\t10\n" +
                "\t\t) AS endTime,\n" +
                "\t\tt_sys_sign_in.ADDRESS AS address,\n" +
                "\t\tt_sys_sign_in.LONGITUDE AS longitude,\n" +
                "\t\tt_sys_sign_in.LATITUDE AS latitude,\n" +
                "\t\tt_sys_sign_in.SIGN_IN_ALWAYS_DISTANCE AS signInAlwaysDistance,\n" +
                "\t\tt_sys_sign_in.SIGN_URL AS signUrl,\n" +
                "\t\tt_sys_sign_in.SIGN_OUT_URL AS signOutUrl,\n" +
                "\t\t'0' AS signState\n" +
                "\tFROM\n" +
                "\t\tt_sys_user\n" +
                "\tLEFT JOIN t_sys_sign_in ON t_sys_sign_in.USER_ID = t_sys_user.ID\n" +
                "\tWHERE\n" +
                "\t\tt_sys_sign_in.SIGN_DATE = '" + dateTime + "'\n" +
                "\tUNION ALL\n" +
                "\t\tSELECT\n" +
                "\t\t\tt_sys_user.ID AS managerId,\n" +
                "\t\t\t'' AS signCreateTime,\n" +
                "\t\t\tLEFT (t_sys_leave.BEGIN_TIME, 10) AS beginTime,\n" +
                "\t\t\tLEFT (t_sys_leave.END_TIME, 10) AS endTime,\n" +
                "\t\t\tt_sys_leave.REMARK AS address,\n" +
                "\t\t\t'' AS longitude,\n" +
                "\t\t\t'' AS latitude,\n" +
                "\t\t\t'' AS signInAlwaysDistance,\n" +
                "\t\t\t'' AS signUrl,\n" +
                "\t\t\t'' AS signOutUrl,\n" +
                "\t\t\t'1' AS signState\n" +
                "\t\tFROM\n" +
                "\t\t\tt_sys_user\n" +
                "\t\tLEFT JOIN t_sys_leave ON t_sys_leave.USER_ID = t_sys_user.ID\n" +
                "\t\tWHERE\n" +
                "\t\t\tt_sys_leave.LEAVE_DATE = '" + dateTime + "'\n" +
                ") AS sign ON manager.managerId = sign.managerId");
        return super.queryForJsonObject(sql.toString());
    }

    public List<JSONObject> getUserIdByRegionId(String regionId, String roleName, String userIds) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\tv_sys_user.id AS userId\n" +
                "FROM\n" +
                "\tt_sys_user_2_region\n" +
                "LEFT JOIN t_sys_region ON t_sys_region.ID=t_sys_user_2_region.REGION_ID\n" +
                "LEFT JOIN v_sys_user ON v_sys_user.ID=t_sys_user_2_region.USER_ID\n" +
                "\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "AND t_sys_user_2_region.REGION_ID = '" + regionId + "'\n" +
                "AND v_sys_user.roleName='" + roleName + "'\n" +
                "AND v_sys_user.ID IN (" + userIds + ")\n" +
                "GROUP BY v_sys_user.id\n");
        return super.queryForJsonList(sql.toString());
    }

    //区域经理工作报告(区域做主表)
    public List<JSONObject> getEmailByUserIdAndRoleNameForRM(String senderIds, String receiverIds, String dateTime, String regionId) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "region.regionName,\n" +
                "\tregion.regionId,\n" +
                "\tsysRegion.userId,\n" +
                "\tt_sys_user.`NAME` AS readerName,\n" +
                "\tt_sys_user.`ID` AS readerId,\n" +
                "\tsysRegion.userName,\n" +
                "\temail.WId,\n" +
                "\temail.emailId,\n" +
                "\tCASE\n" +
                "WHEN email.READER = '1' THEN\n" +
                "\t'已读'\n" +
                "ELSE\n" +
                "\t'未读'\n" +
                "END AS reader,\n" +
                " CASE\n" +
                "WHEN (\n" +
                "\temail.TITLE IS NOT NULL || email.TITLE != ''\n" +
                ") THEN\n" +
                "\t'已发送'\n" +
                "ELSE\n" +
                "\t'未发送'\n" +
                "END AS titleSend,\n" +
                " CASE\n" +
                "WHEN (\n" +
                "\tt_sys_leave.id IS NOT NULL \n" +
                ") THEN\n" +
                "\t'请假'\n" +
                "WHEN (\n" +
                "\tt_sys_sign_in.id IS  NULL \n" +
                ") THEN\n" +
                "\t'缺勤'\n" +
                "ELSE\n" +
                "\t'出勤'\n" +
                "END AS leaveDate,\n" +
                " IFNULL(emailNum.replyNum, '0') AS replyNum\n" +
                "FROM" +
                "(\n" +
                "\t\tSELECT\n" +
                "\t\t\tt_sys_region.`NAME` AS regionName,\n" +
                "\t\t\tt_sys_region.`ID` AS regionId,\n" +
                "\tt_sys_company.SORT_NO AS companySortNo,\n" +
                "\t\t\tt_sys_region.SORT_NO AS regionSortNo\n" +
                "\t\tFROM\n" +
                "\t\t\tt_sys_region\n" +
                "\t\tLEFT JOIN t_sys_company ON t_sys_company.ID = t_sys_region.COMPANY_ID\n" +
                "\t\tWHERE 1=1\n" +
                "\t\tAND t_sys_region.DEL_FLAG = 0\n" +
                "\t\tAND t_sys_company.DEL_FLAG = 0\n" +
                "\t\tAND t_sys_region.PARENT_ID = '" + regionId + "'\n" +
                "\t) AS region\n" +
                "\tLEFT JOIN (\n" +
                "\t\tSELECT\n" +
                "\t\t\tt_sys_user_2_region.USER_ID AS userId,\n" +
                "\t\t\tt_sys_user.`NAME` AS userName,\n" +
                "\t\t\tt_sys_region.`NAME` AS regionName,\n" +
                "\t\t\tt_sys_region.`ID` AS regionId,\n" +
                "\t\t\tt_sys_region.SORT_NO AS regionSortNo,\n" +
                "\t\t\tt_sys_company.SORT_NO AS companySortNo,\n" +
                "\t\t\tt_sys_region.COMPANY_ID AS companyId\n" +
                "\t\tFROM\n" +
                "\t\t\tt_sys_user_2_region\n" +
                "\t\tLEFT JOIN t_sys_region ON t_sys_region.ID = t_sys_user_2_region.REGION_ID\n" +
                "\t\tLEFT JOIN t_sys_company ON t_sys_company.ID = t_sys_region.COMPANY_ID\n" +
                "\t\tLEFT JOIN t_sys_user ON t_sys_user.ID = t_sys_user_2_region.USER_ID\n" +
                "\t\tWHERE\n" +
                "\t\t\t1 = 1\n" +
                "\t\tAND t_sys_region.DEL_FLAG = 0\n" +
                "\t\tAND t_sys_company.DEL_FLAG = 0\n" +
                "\t\tAND t_sys_region.PARENT_ID = '" + regionId + "'\n" +
                "\t\tAND t_sys_user_2_region.USER_ID IN (" + senderIds + ")\n" +
                "\t) AS sysRegion  ON region.regionId = sysRegion.regionId\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tt_sys_email.CREATE_USER_ID AS senderId,\n" +
                "\t\tt_sys_email_receiver.USER_ID AS receiverId,\n" +
                "\t\tt_wrt_main.ID AS id,\n" +
                "\t\tt_sys_email_receiver.READER,\n" +
                "\t\tt_wrt_main.TITLE,\n" +
                "\t\tt_sys_email.ID AS emailId,\n" +
                "\t\tt_sys_email.DATA_ID AS WId\n" +
                "\tFROM\n" +
                "\t\tt_sys_email_receiver\n" +
                "\tLEFT JOIN t_sys_email ON t_sys_email_receiver.EMAIL_ID = t_sys_email.ID\n" +
                "\tLEFT JOIN t_wrt_main ON t_wrt_main.ID = t_sys_email.DATA_ID\n" +
                "\tWHERE\n" +
                "\t\t1 = 1\n" +
                "\tAND t_wrt_main.flage !=- 1\n" +
                "\tAND t_sys_email.E_TYPE = 1\n" +
                "\tAND t_sys_email_receiver.USER_ID IN (" + receiverIds + ")\n" +
                "\tAND t_sys_email.CREATE_USER_ID IN (" + senderIds + ")\n" +
                "\tAND t_sys_email.REPORT_DATE = '" + dateTime + "'\n" +
                ") AS email ON email.senderId = sysRegion.userId\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tCOUNT(1) AS replyNum,\n" +
                "\t\tDATA_ID\n" +
                "\tFROM\n" +
                "\t\tt_sys_email_receiver\n" +
                "\tWHERE\n" +
                "\t\t1 = 1\n" +
                "\tAND REPLY_TIME IS NOT NULL\n" +
                "\tAND DATA_ID IN (\n" +
                "\t\tSELECT\n" +
                "\t\t\tt_wrt_main.ID\n" +
                "\t\tFROM\n" +
                "\t\t\tt_wrt_main\n" +
                "\t\tWHERE\n" +
                "\t\t\tt_wrt_main.USER_ID IN (" + senderIds + ")\n" +
                "\t\t AND t_sys_email_receiver.USER_ID IN (" + receiverIds + ") \n" +
                "\t\tAND t_wrt_main.REPORT_DATE = '" + dateTime + "'\n" +
                "\t)\n" +
                "\tGROUP BY\n" +
                "\t\tDATA_ID\n" +
                ") AS emailNum ON email.WId = emailNum.DATA_ID\n" +
                "LEFT JOIN t_sys_leave ON sysRegion.userId = t_sys_leave.USER_ID\n" +
                "AND LEAVE_DATE = '" + dateTime + "'\n" +
                "LEFT JOIN t_sys_sign_in ON sysRegion.userId = t_sys_sign_in.USER_ID\n" +
                "AND SIGN_DATE = '" + dateTime + "'\n" +
                "LEFT JOIN t_sys_user ON t_sys_user.ID = email.receiverId\n" +
                "ORDER BY region.companySortNo,region.regionSortNo,email.receiverId\n\n");
        return super.queryForJsonList(sql.toString());
    }


    public List<JSONObject> getRSByRegionIdForSBXQ(String regionId) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\tv_sys_user.id AS userId,\n" +
                "\tv_sys_user.`NAME` AS userName\n" +
                "FROM\n" +
                "\tv_sys_user\n" +
                "LEFT JOIN t_sys_user_2_region ON t_sys_user_2_region.USER_ID = v_sys_user.id\n" +
                "LEFT JOIN t_sys_region ON t_sys_region.ID = t_sys_user_2_region.REGION_ID\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "AND t_sys_region.DEL_FLAG = 0\n" +
                "AND t_sys_region.PARENT_ID = 0\n" +
                "AND v_sys_user.roleName IN ('区域支持')\n" +
                "AND t_sys_user_2_region.REGION_ID in (" + regionId + ")\n" +
                "GROUP BY\n" +
                "\tv_sys_user.id\n");
        return super.queryForJsonList(sql.toString());

    }

    public List<JSONObject> getRegionIdByUserIdForSBXQ(String userId) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\tt_sys_region.id AS regionId,\n" +
                "\tt_sys_region.NAME AS regionName\n" +
                "FROM\n" +
                "\tt_sys_user_2_region\n" +
                "LEFT JOIN t_sys_region ON t_sys_region.ID = t_sys_user_2_region.REGION_ID\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "AND t_sys_region.DEL_FLAG = 0\n" +
                "AND t_sys_region.PARENT_ID = 0\n" +
                "AND t_sys_user_2_region.USER_ID IN (" + userId + ")\n" +
                "GROUP BY\n" +
                "\tt_sys_region.id\n" +
                "ORDER BY\n" +
                "\tt_sys_region.SORT_NO\n");
        return super.queryForJsonList(sql.toString());

    }

    public Integer getSendEmail(String userId, String dateTime) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\tcount(1) AS count\n" +
                "FROM\n" +
                "\tt_sys_email\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "AND REPORT_DATE = '" + dateTime + "'\n" +
                "AND t_sys_email.CREATE_USER_ID = '" + userId + "'\n" +
                "AND t_sys_email.E_TYPE = 1\n");
        String count = super.queryForString(sql.toString(), new Object[]{});
        return Integer.parseInt(count);
    }
}